﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports BusinessRule.DTO
Imports System.Configuration
Imports DatabaseAccess.DBUtility

Public Class uMESContainerReportBusiness
    Implements uMESContainerReportIBusiness

    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

    ''' <summary>
    ''' 获取零件进度表主界面数据-旧
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerReportMainInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetContainerReportMainInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strProductName, strProductFamilyname, strProducttypename, strFactoryName, strStartDate, strEndDate As String
            Dim boolDispClose As Boolean = False
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strProductName = ""
            If QueryCondition.Keys.Contains("ProductName") = True Then
                strProductName = QueryCondition("ProductName").ToString()
            End If

            strProductFamilyname = ""
            If QueryCondition.Keys.Contains("ProductFamilyName") = True Then
                strProductFamilyname = QueryCondition("ProductFamilyName").ToString()
            End If

            strProducttypename = ""
            If QueryCondition.Keys.Contains("ProductTypeName") = True Then
                strProducttypename = QueryCondition("ProductTypeName").ToString()
            End If

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If

            If QueryCondition.Keys.Contains("DispClose") = True Then
                boolDispClose = QueryCondition("DispClose")
            End If

            strStartDate = ""
            If QueryCondition.Keys.Contains("StartDate") = True Then
                strStartDate = QueryCondition("StartDate").ToString()
            End If

            strEndDate = ""
            If QueryCondition.Keys.Contains("EndDate") = True Then
                strEndDate = QueryCondition("EndDate").ToString()
            End If
            strSQL = "SELECT Rownum,CTemp.Containername, CTemp.Productname, CTemp.Description,CTemp.Productfamilyname, CTemp.Factoryname,CTemp.Qty, CTemp.Uomname, " & vbCrLf & _
                     "       CTemp.Producttypename, CTemp.Plannedcompletiondate ,  CTemp.ComPercent," & vbCrLf & _
                     "       CTemp.AllStepNum, CTemp.ComStepNum, CTemp.Mfgordername," & vbCrLf & _
                     "       CTemp.Containerid, CTemp.ChildNum, CTemp.Orderstatusname" & vbCrLf & _
                     "FROM" & vbCrLf & _
                     "(" & vbCrLf & _
                     "SELECT CInfo.Containername, CInfo.Productname, CInfo.Description,CInfo.Productfamilyname, CInfo.Factoryname,CInfo.Qty, CInfo.Uomname, " & vbCrLf & _
                     "        CInfo.Producttypename, TO_CHAR(CInfo.Plannedcompletiondate, 'RRRR-MM-DD') AS Plannedcompletiondate , (Round(NVL(CMove.ComStepNum, 0) / NVL(CStepAll.AllStepNum, 1) *100 , 0) || '%' ) AS ComPercent," & vbCrLf & _
                     "       NVL(CStepAll.AllStepNum, 1) AS AllStepNum, NVL(CMove.ComStepNum, 0) AS ComStepNum, " & vbCrLf & _
                     "      CInfo.Mfgordername," & vbCrLf & _
                     "       CInfo.Containerid, NVL(CChild.ChildNum, 0) AS ChildNum, CInfo.Orderstatusname" & vbCrLf & _
                     "FROM" & vbCrLf & _
                     "(SELECT Container.Containername, Productbase.Productname, Product.Description,  " & vbCrLf & _
                     "       Mfgorder.Mfgordername, ProductType.Producttypename," & vbCrLf & _
                     "       Container.Qty, UOM.Uomname,Productfamily.Productfamilyname, Factory.Factoryname, Mfgorder.Plannedcompletiondate, Container.Containerid,  Orderstatus.Orderstatusname" & vbCrLf & _
                     "FROM Container" & vbCrLf & _
                     "INNER JOIN Product ON Container.Productid = Product.Productid" & vbCrLf & _
                     "INNER JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid" & vbCrLf & _
                     "LEFT OUTER JOIN Mfgorder ON Mfgorder.Mfgorderid = Container.Mfgorderid" & vbCrLf & _
                     "LEFT OUTER JOIN ProductType ON ProductType.ProductTypeid = Product.ProductTypeid" & vbCrLf & _
                     "LEFT OUTER JOIN UOM ON UOM.UOMID = Container.UOMID" & vbCrLf & _
                     "LEFT OUTER JOIN Productfamily ON Productfamily.Productfamilyid = Product.Productfamilyid" & vbCrLf & _
                     "INNER JOIN Currentstatus ON  Currentstatus.Currentstatusid = Container.Currentstatusid" & vbCrLf & _
                     "LEFT OUTER JOIN Factory ON Currentstatus.Factoryid = Factory.Factoryid " & vbCrLf & _
                     "LEFT OUTER JOIN Orderstatus ON Mfgorder.Orderstatusid = Orderstatus.Orderstatusid" & vbCrLf & _
                     "WHERE Container.Parentcontainerid IS NULL " & vbCrLf

            If strContainerName <> "" Then
                strSQL += " AND Container.Containername LIKE '%" & strContainerName & "%'" & vbCrLf
            End If
            If strProductFamilyname <> "" Then
                strSQL += " AND Productfamily.Productfamilyname = '" & strProductFamilyname & "'" & vbCrLf
            End If
            If strProductName <> "" Then
                strSQL += " AND Productbase.Productname LIKE '%" & strProductName & "%'" & vbCrLf
            End If
            If strProducttypename <> "" Then
                strSQL += " AND ProductType.Producttypename='" & strProducttypename & "'" & vbCrLf
            End If
            If strFactoryName <> "" Then
                strSQL += " AND Factory.FactoryName ='" & strFactoryName & "'" & vbCrLf
            End If

            If boolDispClose = False Then
                strSQL += " AND Container.status=1 " & vbCrLf
            End If

            strSQL = strSQL & " ) CInfo" & vbCrLf & _
                     "LEFT OUTER JOIN " & vbCrLf & _
                     "(SELECT CMoveTemp.Containername, COUNT(CMoveTemp.Workflowstepid) AS ComStepNum" & vbCrLf & _
                    "FROM" & vbCrLf & _
                    "(SELECT DISTINCT Container.Containername,  Historymainline.Workflowstepid" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "INNER JOIN Historymainline ON Container.Containerid = Historymainline.Containerid" & vbCrLf & _
                    "INNER JOIN Speclastcheckhistory ON Speclastcheckhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "WHERE Historymainline.Status = 1" & vbCrLf & _
                    ") CMoveTemp" & vbCrLf & _
                    "GROUP BY CMoveTemp.Containername ) CMove" & vbCrLf & _
                     "ON CMove.Containername = CInfo.Containername" & vbCrLf & _
                     "LEFT OUTER JOIN " & vbCrLf & _
                     "(" & vbCrLf & _
                     "SELECT CStepAllTemp.Containername, COUNT(CStepAllTemp.Workflowstepname) AS AllStepNum" & vbCrLf & _
                     "FROM" & vbCrLf & _
                     "(SELECT Container.Containername, WorkflowstepAll.Workflowstepname" & vbCrLf & _
                     "FROM Container" & vbCrLf & _
                     "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid" & vbCrLf & _
                     "INNER JOIN Workflowstep ON Workflowstep.Workflowstepid = Currentstatus.Workflowstepid" & vbCrLf & _
                     "INNER JOIN Workflowstep WorkflowstepAll ON WorkflowstepAll.Workflowid = Workflowstep.Workflowid " & vbCrLf & _
                     "WHERE Container.Parentcontainerid IS NULL" & vbCrLf & _
                     "UNION ALL" & vbCrLf & _
                     "SELECT ParentContainer.Containername, WorkflowstepAll.Workflowstepname" & vbCrLf & _
                     "FROM Container" & vbCrLf & _
                     "INNER JOIN Container ParentContainer ON ParentContainer.Containerid = Container.Parentcontainerid" & vbCrLf & _
                     "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid" & vbCrLf & _
                     "INNER JOIN Workflowstep ON Workflowstep.Workflowstepid = Currentstatus.Workflowstepid" & vbCrLf & _
                     "INNER JOIN Workflowstep WorkflowstepAll ON WorkflowstepAll.Workflowid = Workflowstep.Workflowid " & vbCrLf & _
                     "WHERE Container.Parentcontainerid IS NOT NULL" & vbCrLf & _
                     ") CStepAllTemp" & vbCrLf & _
                     "GROUP BY CStepAllTemp.Containername) CStepAll" & vbCrLf & _
                     "ON CStepAll.Containername = CInfo.Containername" & vbCrLf & _
                     "LEFT OUTER JOIN " & vbCrLf & _
                     "(" & vbCrLf & _
                     "SELECT ParentContainer.Containername AS Ckparentcontainername, Count(Container.Containername) as ChildNum" & vbCrLf & _
                     "FROM Container" & vbCrLf & _
                     "INNER JOIN Container ParentContainer ON ParentContainer.Containerid = Container.Parentcontainerid" & vbCrLf & _
                     "WHERE  Container.Parentcontainerid IS NOT NULL " & vbCrLf & _
                     "GROUP BY ParentContainer.Containername " & vbCrLf & _
                     ") CChild" & vbCrLf & _
                     "ON CChild.Ckparentcontainername = CInfo.Containername" & vbCrLf & _
                     "WHERE 1=1 " & vbCrLf

            strSQL += "ORDER BY CInfo.Producttypename, CInfo.Plannedcompletiondate, CInfo.Containername " & vbCrLf & _
                      ") CTemp " & vbCrLf

            Dim dtMain As DataTable = dal.GetCheckTempInfo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtMain)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

#Region "零件进度表数据查询"
    Public Function GetConReportDetailInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetConReportDetailInfo

        Dim result = GetMainData(QueryCondition)
        Dim mainTable As DataTable = result.DBTable.Copy()

        Dim strContainerList As String = ""
        Dim strContainerIDList As String = ""
        For Each row As DataRow In mainTable.Rows
            strContainerList = strContainerList + "'" + row("Containername").ToString().Trim() + "',"
            strContainerIDList = strContainerIDList + "'" + row("containerid").ToString().Trim() + "',"
        Next

        If strContainerList.Length > 0 Then
            strContainerList = strContainerList.Substring(0, strContainerList.Length - 1)
            strContainerIDList = strContainerIDList.Substring(0, strContainerIDList.Length - 1)
        Else
            result.Message = "没有查询到需要的数据"
            result.Result = False
            Return result
        End If

        Dim strSql As String = " SELECT csr.containername,csr.dispatchqty qty,NVL(csr.reportqty,0) reportedqty,csr.containerid,csr.specid,csr.workflowstepid,csr.workflowname||':'||csr.workflowrevision workflow,csr.workflowid FROM containersteprecord csr WHERE csr.containerid IN (" & strContainerIDList & ") " 'add:Wangjh 2017-8-16
        Dim dispatchTable As DataTable = dal.GetCheckTempInfo(strSql)

        '查询转工 
        '增加csr.toacceptdate,csre.completedate查询 tianFW 2018年7月3日
        strSql = " SELECT csre.containerid,csre.recivedate returndate,to_char(csr.senddate) senddate,NULL synergicinfoname,s.specid,ws3.workflowstepid," &
                 " csr.toacceptdate,csre.completedate,ws.workflowid,wb.workflowname||':'||w.workflowrevision workflow" &
                 " FROM containershiprecord csr " &
                 " LEFT JOIN containershiprecive csre ON csre.containershiprecordid=csr.containershiprecordid" &
                 " LEFT JOIN workflowstep ws ON ws.workflowstepid=csr.workflowstepid" &
                 " LEFT JOIN WORKFLOWSTEP WS2 ON WS2.workflowstepid=csr.TOworkflowstepid " &
                 " LEFT JOIN WORKFLOWSTEP WS3 ON WS3.WORKFLOWID=WS.WORKFLOWID AND (WS3.sequence>=ws.sequence AND ws3.sequence<=ws2.sequence)" &
                 " LEFT JOIN specbase sb ON sb.specbaseid=ws3.specbaseid" &
                 " LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws3.specid)" &
                 " left join workflow w on w.workflowid=ws.workflowid" &
                 " left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid" &
                 " WHERE CSR.ISUSE=0 AND  csre.containerid IN (" & strContainerIDList & ")" 'add:Wangjh 2017-8-16
        If QueryCondition.ContainsKey("FactoryID") Then
            strSql += String.Format(" AND CSR.factoryid ='{0}'", QueryCondition("FactoryID"))
        End If

        strSql += "UNION"

        strSql += " SELECT csr.containerid,csre.recivedate returndate,to_char(csr.senddate) senddate,NULL synergicinfoname,s.specid,ws3.workflowstepid," &
                 " csr.toacceptdate,csre.completedate,ws.workflowid,wb.workflowname||':'||w.workflowrevision workflow" &
                 " FROM containershiprecord csr " &
                 " LEFT JOIN containershiprecive csre ON csre.containershiprecordid=csr.containershiprecordid and csre.containerid=csr.containerid" &
                 " LEFT JOIN workflowstep ws ON ws.workflowstepid=csr.workflowstepid" &
                 " LEFT JOIN WORKFLOWSTEP WS2 ON WS2.workflowstepid=csr.TOworkflowstepid " &
                 " LEFT JOIN WORKFLOWSTEP WS3 ON WS3.WORKFLOWID=WS.WORKFLOWID AND (WS3.sequence>=ws.sequence AND ws3.sequence<=ws2.sequence)" &
                 " LEFT JOIN specbase sb ON sb.specbaseid=ws3.specbaseid" &
                 " LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws3.specid)" &
                 " left join workflow w on w.workflowid=ws.workflowid" &
                 " left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid" &
                 " WHERE CSR.ISUSE=0 AND csr.containerid IN (" & strContainerIDList & ")" 'add:Wangjh 2017-8-16
        If QueryCondition.ContainsKey("FactoryID") Then
            strSql += String.Format(" AND CSR.factoryid ='{0}'", QueryCondition("FactoryID"))
        End If
        strSql += " UNION "
        strSql += <string>SELECT cci.containerid,csre.recivedate returndate,to_char(csr.senddate) senddate,NULL synergicinfoname,s.specid,ws3.workflowstepid, 
csr.toacceptdate,csre.completedate,ws.workflowid,wb.workflowname||':'||w.workflowrevision workflow 
FROM containershiprecord csr  
LEFT JOIN containershiprecive csre ON csre.containershiprecordid=csr.containershiprecordid and csre.containerid=csr.containerid 
LEFT JOIN workflowstep ws ON ws.workflowstepid=csr.workflowstepid 
LEFT JOIN WORKFLOWSTEP WS2 ON WS2.workflowstepid=csr.TOworkflowstepid  
LEFT JOIN WORKFLOWSTEP WS3 ON WS3.WORKFLOWID=WS.WORKFLOWID AND WS3.sequence BETWEEN ws.sequence AND ws2.sequence
LEFT JOIN specbase sb ON sb.specbaseid=ws3.specbaseid 
LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws3.specid) 
left join workflow w on w.workflowid=ws.workflowid 
left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid 
LEFT JOIN containercurrentinfo cci ON cci.shipid=csr.containershiprecordid
WHERE CSR.ISUSE=0 </string>.Value
        strSql += " AND cci.containerid IN (" & strContainerIDList & ")"
        If QueryCondition.ContainsKey("FactoryID") Then
            strSql += String.Format(" AND (csr.factoryid ='{0}' or csr.tofactoryid='{0}' )", QueryCondition("FactoryID"))
        End If

        '转工
        Dim synerTable As DataTable = dal.GetCheckTempInfo(strSql)

        '增加外委数据 add:Wangjh 20171101
        strSql = <string>
                     SELECT ws3.workflowstepid,ws3.workflowstepname,CMM.Containerid,CMM.CONTAINERNAME,CMM.INDATE,CMM.INEMPLOYEEID,CMM.LOSSQTY,CMM.OUTCUSTOMERID,
                    CMM.OUTDATE,CMM.OUTQTY,ws.workflowid,wb.workflowname||':'||w.workflowrevision workflow FROM CONTAINERCOMMISSIONING CMM
                    LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CMM.WORKFLOWSTEPID
                    LEFT JOIN WORKFLOWSTEP WS2 ON WS2.WORKFLOWSTEPID=CMM.INWORKFLOWSTEPID
                    left join workflow w on w.workflowid=ws.workflowid
                    left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
                 </string>
        strSql += " LEFT JOIN workflowstep ws3 ON ws3.workflowid=ws.workflowid AND(ws3.sequence>=ws.sequence AND ws3.sequence<ws2.sequence) WHERE CMM.ISUSE = 0"
        strSql += String.Format(" and cmm.containerid IN ({0})", strContainerIDList)
        Dim commissionDt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        '记录工艺 add:Wangjh 
        Dim machineWorkflowDt As New DataTable
        Dim containerAndWorkflow As New DataTable
        If QueryCondition.ContainsKey("ContainerName") Then
            machineWorkflowDt.Columns.Add("workflow")
            machineWorkflowDt.Columns.Add("workflowID")
            machineWorkflowDt.Columns.Add("cuworkflowID")
            machineWorkflowDt.Columns("cuworkflowID").DefaultValue = mainTable.Rows(0)("workflowid")
            '
            'Dim dispatchTableTemp As DataTable = dispatchTable.DefaultView.ToTable(True, "workflow", "workflowid")
            'machineWorkflowDt.Merge(dispatchTableTemp) '存储工艺
            'Dim synerTableTemp As DataTable = synerTable.DefaultView.ToTable(True, "workflow", "workflowid")
            'machineWorkflowDt.Merge(synerTableTemp) '存储工艺
            'Dim commissionDtTemp As DataTable = commissionDt.DefaultView.ToTable(True, "workflow", "workflowid")
            'machineWorkflowDt.Merge(commissionDtTemp) '存储工艺
            'machineWorkflowDt.AcceptChanges()
            'machineWorkflowDt = CombineTable(machineWorkflowDt, New DataTable)
            'machineWorkflowDt.TableName = "MachineWorkflow"

            containerAndWorkflow.Columns.Add("workflow")
            containerAndWorkflow.Columns.Add("workflowID")
            containerAndWorkflow.Columns.Add("ContainerID")
            '
            Dim dispatchTableTemp = dispatchTable.DefaultView.ToTable(True, "containerid", "workflow", "workflowid")
            containerAndWorkflow.Merge(dispatchTableTemp) '存储工艺
            Dim synerTableTemp = synerTable.DefaultView.ToTable(True, "containerid", "workflow", "workflowid")
            containerAndWorkflow.Merge(synerTableTemp) '存储工艺
            Dim commissionDtTemp = commissionDt.DefaultView.ToTable(True, "containerid", "workflow", "workflowid")
            containerAndWorkflow.Merge(commissionDtTemp) '存储工艺
            containerAndWorkflow = CombineTable(containerAndWorkflow, New DataTable)
            containerAndWorkflow.AcceptChanges()

            '加工工艺
            machineWorkflowDt = containerAndWorkflow.Copy
            machineWorkflowDt.TableName = "MachineWorkflow"
            machineWorkflowDt.AcceptChanges()

        End If
        'add AND CTD.WORKFLOWSTEPID = WP1.WORKFLOWSTEPID tianFW 2018年1月12日
        Dim specTable As New DataTable
        specTable = GetStepTable(strContainerList, containerAndWorkflow, QueryCondition)
        '以下逻辑是先取批次的件号，然后显示的工序格式是 原工序名（ProName-SpecNO-SepcName）.replace("ProName-","")逻辑处理，
        '如果不满足，则还是按原来的逻辑处理 tianfw 2018年10月31日
        Dim container_product_dt As DataTable = mainTable.DefaultView.ToTable("true", {"Containername", "Productname"})
        For Each row As DataRow In specTable.Rows
            For Each cpdr As DataRow In container_product_dt.Rows
                If row("containername") = cpdr("Containername") And row("workflowstepname").ToString().Contains(cpdr("Productname").ToString()) Then
                    row("workflowstepname") = row("workflowstepname").ToString().Replace(cpdr("Productname").ToString() + "-", "")
                    Exit For
                End If
            Next
            '原处理逻辑 屏蔽 tianfw 2019年1月16日
            'If row("workflowstepname").ToString().Contains("--") Then
            '    Dim workflowstepname As String = row("workflowstepname").ToString()
            '    row("workflowstepname") = workflowstepname.Substring(workflowstepname.IndexOf("-") + 1)
            'Else
            '    Dim arrayS As String() = row("workflowstepname").ToString().Trim().Split("-")
            '    Dim intLength As Integer = arrayS.Length
            '    Dim i As Integer = 0
            '    If intLength >= 3 Then
            '        row("workflowstepname") = arrayS(intLength - 2) & "-" & arrayS(intLength - 1)
            '    End If
            'End If
        Next

        Dim specIDList As String = ""
        Dim stepIDList As String = ""

        For Each row As DataRow In specTable.Rows
            specIDList = specIDList + "'" + row("specid").ToString().Trim() + "',"
            stepIDList = stepIDList + "'" + row("workflowstepid").ToString().Trim() + "',"
        Next
        If stepIDList.Length > 0 Then
            specIDList = specIDList.Substring(0, specIDList.Length - 1)
            stepIDList = stepIDList.Substring(0, stepIDList.Length - 1)
        Else
            stepIDList = "' '"
            specIDList = "' '"
        End If

        '查完工信息
        'modify tianFW 2018年3月27日 查询语句关键字 IN 参数数量不能超过1000个
        Dim stepstr_ As String() = stepIDList.Split(",")
        Dim stepstr As New StringBuilder
        For i As Integer = 0 To stepstr_.Length - 1
            If stepstr.Length = 0 Then
                stepstr.AppendLine("(")
            Else
                stepstr.AppendLine(" OR ")
            End If
            stepstr.AppendFormat("csr.workflowstepid={0}", stepstr_(i))
        Next
        stepstr.AppendLine(")")
        strSql = " 	SELECT csr.passqty qualifiedqty,csr.containerid,csr.specid,csr.workflowstepid,csr.usecompleted FROM containersteprecord csr " &
                 "  WHERE csr.containerid IN (" & strContainerIDList & ") AND " & stepstr.ToString() & " AND csr.checkemployeeid IS NOT NULL" 'add:Wangjh 2017-8-16
        Dim speCheckTable As DataTable = dal.GetCheckTempInfo(strSql)


        Dim maxColum As Integer = 0

        For Each row As DataRow In mainTable.Rows
            Dim strFilter As String
            Dim stepRows As DataRow()
            strFilter = String.Format("containername='{0}'", row("containername").ToString().Trim())
            stepRows = specTable.Select(strFilter, "sequence asc")
            If stepRows.Length > maxColum Then
                maxColum = stepRows.Length
            End If
        Next

        Dim returnTable As DataTable = New DataTable()
        returnTable.Columns.Add("containerid")
        returnTable.Columns.Add("Containername")
        returnTable.Columns.Add("Productname")
        returnTable.Columns.Add("Description")
        returnTable.Columns.Add("Factoryname")
        returnTable.Columns.Add("Qty")
        returnTable.Columns.Add("originalstartdate")
        returnTable.Columns.Add("cdoname")
        returnTable.Columns.Add("state")    '状态
        returnTable.Columns.Add("ishold") '批次状态 暂停 
        returnTable.Columns.Add("workflowstepname")
        returnTable.Columns.Add("perent")
        returnTable.Columns.Add("sequence")
        returnTable.Columns.Add("specid")
        returnTable.Columns.Add("workflowid")

        For i As Integer = 1 To maxColum
            returnTable.Columns.Add("A" & i.ToString().Trim(), Type.GetType("System.String"))
        Next
        Dim strFinishFlowName As String = ConfigurationSettings.AppSettings("FinishFlowName")

        Dim circleDt As New DataTable
        If QueryCondition.ContainsKey("WorkflowID") Then '只查此工艺的加工记录
            circleDt = mainTable.Clone
            Dim conDt As DataTable = containerAndWorkflow.DefaultView.ToTable(True, "ContainerID")
            For Each conDr As DataRow In conDt.Rows
                Dim drs As DataRow() = mainTable.Select("ContainerID='" + conDr("ContainerID") + "'")
                If drs.Length > 0 Then
                    circleDt.ImportRow(drs(0))
                End If

            Next
            circleDt.AcceptChanges()
            If circleDt.Rows.Count = 0 Then
                circleDt = mainTable.Copy
            End If
        Else
            circleDt = mainTable.Copy
        End If
        For Each row As DataRow In circleDt.Rows
            Dim cuWorkflowName As String = "" '当前序 add by tianFW 2018年7月2日
            Dim strFilter As String
            Dim stepRows As DataRow()
            '增加承制分厂转工判断只取转工的工序 add：WangJh
            If row("isship") = 1 And row("isinstore") = "0" Then '是转工的批次且是承制分厂
                Dim iscontinue As Boolean = True
                If QueryCondition.Keys.Contains("FactoryID") Then
                    If row("tofactoryid") = QueryCondition("FactoryID") Then
                        Dim shipSql As String = <string>
                                            SELECT DISTINCT csrs.workflowstepid,ws.workflowstepname,ws.sequence,s.specid,sb.revofrcdid,css.noship,ws.wipmsglabel
											FROM containershiprecordstep csrs
											LEFT JOIN containershiprecord csr ON csr.containershiprecordid = csrs.containershiprecordid
											LEFT JOIN containerstepnoship css ON css.containerid = csr.containerid AND css.workflowstepid = csrs.workflowstepid
                                            LEFT JOIN workflowstep ws ON ws.workflowstepid=csrs.workflowstepid
                                            LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid 
                                            LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                                        </string>
                        shipSql += String.Format(" WHERE csrs.containershiprecordid='{0}'", row("containershiprecordid"))
                        shipSql += " order by ws.sequence"
                        Dim shipDt As DataTable = DatabaseHelper.Query_ReturnDatTable(shipSql)
                        stepRows = shipDt.Select
                        iscontinue = False
                    End If
                End If

                If iscontinue Then
                    If row.IsNull("SPLITSequence") Then
                        strFilter = String.Format("containername='{0}'", row("containername").ToString().Trim())
                    Else
                        strFilter = String.Format("containername='{0}' and Sequence>={1}", row("containername").ToString().Trim(), row("SPLITSequence").ToString().Trim())
                    End If
                    stepRows = specTable.Select(strFilter, "sequence asc")
                End If

                '     If row("tofactoryid") = QueryCondition("FactoryID") Then
                '         Dim shipSql As String = <string>
                '                                 SELECT DISTINCT csrs.workflowstepid,ws.workflowstepname,ws.sequence,s.specid,sb.revofrcdid,css.noship,ws.wipmsglabel
                'FROM containershiprecordstep csrs
                'LEFT JOIN containershiprecord csr ON csr.containershiprecordid = csrs.containershiprecordid
                'LEFT JOIN containerstepnoship css ON css.containerid = csr.containerid AND css.workflowstepid = csrs.workflowstepid
                '                                 LEFT JOIN workflowstep ws ON ws.workflowstepid=csrs.workflowstepid
                '                                 LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid 
                '                                 LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                '                             </string>
                '         shipSql += String.Format(" WHERE csrs.containershiprecordid='{0}'", row("containershiprecordid"))
                '         shipSql += " order by ws.sequence"
                '         Dim shipDt As DataTable = DatabaseHelper.Query_ReturnDatTable(shipSql)
                '         stepRows = shipDt.Select
                '     Else
                '         If row.IsNull("SPLITSequence") Then
                '             strFilter = String.Format("containername='{0}'", row("containername").ToString().Trim())
                '         Else
                '             strFilter = String.Format("containername='{0}' and Sequence>={1}", row("containername").ToString().Trim(), row("SPLITSequence").ToString().Trim())
                '         End If
                '         stepRows = specTable.Select(strFilter, "sequence asc")
                '     End If
            Else
                If row.IsNull("SPLITSequence") Or row("isinstore") <> "0" Then
                    strFilter = String.Format("containername='{0}'", row("containername").ToString().Trim())
                Else
                    strFilter = String.Format("containername='{0}' and Sequence>={1}", row("containername").ToString().Trim(), row("SPLITSequence").ToString().Trim())
                End If
                stepRows = specTable.Select(strFilter, "sequence asc")
            End If

            Dim rowTmp As DataRow = returnTable.NewRow()
            Dim newrow As DataRow = returnTable.NewRow()
            If row("isinstore") <> "0" Then              ' 处理物料领料和审核阶段
                newrow("containerid") = row("containerid")
                newrow("Containername") = row("Containername")
                newrow("Productname") = row("Productname")
                newrow("Description") = row("Description")
                newrow("Factoryname") = row("Factoryname")
                newrow("Qty") = row("Qty")
                newrow("ishold") = row("ishold")
                newrow("originalstartdate") = row("originalstartdate")
                newrow("cdoname") = row("cdoname")
                newrow("workflowid") = row("workflowid")
                If row("workflowname").ToString().Trim() = strFinishFlowName Then
                    newrow("state") = "6" '入库
                    returnTable.Rows.Add(newrow)
                Else
                    newrow("state") = "0" '物料领料
                    returnTable.Rows.Add(newrow)
                    Continue For
                End If
            Else 'add:Wangjh 2017-8-25
                rowTmp("containerid") = row("containerid")
                rowTmp("Containername") = row("Containername")
                rowTmp("Productname") = row("Productname")
                rowTmp("Description") = row("Description")
                rowTmp("Factoryname") = row("Factoryname")
                rowTmp("Qty") = row("Qty")
                rowTmp("ishold") = row("ishold")
                rowTmp("originalstartdate") = row("originalstartdate")
                rowTmp("cdoname") = row("cdoname")
                rowTmp("state") = "1"
                rowTmp("workflowid") = row("workflowid")
            End If
            Dim TyprValue As String = "" '是否是不进行工序 1 子工艺工序 2 子工艺不加工序，弹出页面传参
            For i As Int32 = 0 To stepRows.Length - 1
                TyprValue = ""
                Dim strSpecID = stepRows(i)("specID").ToString
                Dim strSpecName = stepRows(i)("workflowstepname").ToString
                Dim strUrl = ""
                '如果是不进行序。则传类型，打开页面根据类型显示控件并展示数据 add
                If Not (stepRows(i).IsNull("noship") And stepRows(i).IsNull("wipmsglabel")) Then
                    If Not stepRows(i).IsNull("noship") Then
                        If Convert.ToString(stepRows(i)("noship")) = "2" Then '子工艺不加工序
                            TyprValue = "2"
                        End If
                    Else
                        If Not stepRows(i).IsNull("wipmsglabel") Then
                            If Convert.ToString(stepRows(i)("wipmsglabel")) = "子工艺" Then '子工艺工序
                                TyprValue = "1"
                            End If
                        End If
                    End If
                End If
                'modify by tianFW 如果是当前序，则显示为黄色 2018年7月2日
                If row("cuworkflowstepid") <> stepRows(i)("WorkFlowStepID").ToString().Trim() Then
                    strUrl = "<a href='#' onclick =openSpecDetailwinsNew('" & row("containerid").ToString().Trim() & "','" & strSpecID & "','" & stepRows(i)("sequence").ToString().Trim() & "','" & stepRows(i)("WorkFlowStepID").ToString().Trim() & "','" & stepRows(i)("WorkFlowStepName").ToString().Trim() & "','" & TyprValue & "')  style='font-size: x-small;height:10px;text-align: center;color:Blue;'>" & strSpecName & "</a>"
                Else
                    strUrl = "<a href='#' onclick =openSpecDetailwinsNew('" & row("containerid").ToString().Trim() & "','" & strSpecID & "','" & stepRows(i)("sequence").ToString().Trim() & "','" & stepRows(i)("WorkFlowStepID").ToString().Trim() & "','" & stepRows(i)("WorkFlowStepName").ToString().Trim() & "','" & TyprValue & "')  style='font-size: x-small;height:10px;text-align: center;color:Red;'>" & strSpecName & "</a>"
                    cuWorkflowName = strSpecName
                End If
                If Not rowTmp.IsNull("containerid") Then 'add:Wangjh 2017-8-25
                    rowTmp("A" & (i + 1).ToString()) = strUrl
                Else
                    newrow("A" & (i + 1).ToString()) = strUrl
                End If
            Next
            If Not rowTmp.IsNull("containerid") Then 'add:Wangjh 2017-8-25
                returnTable.Rows.Add(rowTmp)
            End If

            rowTmp = returnTable.NewRow() '批次工序第二列
            rowTmp("containerid") = ""
            rowTmp("Containername") = cuWorkflowName '在批次号下面显示当前序名称 Add by tianFW 2018年7月2日
            rowTmp("Productname") = ""
            rowTmp("Description") = row("WORKCENTERNAME")
            rowTmp("Factoryname") = ""
            rowTmp("Qty") = ""
            rowTmp("ishold") = ""
            rowTmp("originalstartdate") = "'"
            rowTmp("cdoname") = ""
            rowTmp("state") = ""
            For i As Int32 = 0 To stepRows.Length - 1
                '如果存在时子工艺工序或者子工艺不加工序   屏蔽子工艺不加工序采用子工艺序颜色表示逻辑 2019年1月15日 tianFW 
                'If Not (stepRows(i).IsNull("noship") And stepRows(i).IsNull("wipmsglabel")) Then
                '    If Not stepRows(i).IsNull("noship") Then
                '        If Convert.ToString(stepRows(i)("noship")) = "2" Then '子工艺不加工序
                '            rowTmp("A" & (i + 1).ToString()) = "15"
                '            Continue For
                '        End If
                '    ElseIf Not stepRows(i).IsNull("wipmsglabel") Then
                If Convert.ToString(stepRows(i)("wipmsglabel")) = "子工艺" Then '子工艺工序 如果存在转工记录，则会进入转工记录标识逻辑处理
                    rowTmp("A" & (i + 1).ToString()) = "15"
                    'End If
                    '    End If
                End If
                strFilter = String.Format("containerid='{0}' and workflowstepid='{1}'", row("containerid").ToString().Trim(), stepRows(i)("workflowstepid").ToString().Trim())
                Dim dispatRows As DataRow() = dispatchTable.Select(strFilter)
                Dim synerRows As DataRow() = synerTable.Select(strFilter)

                'Add by wzj 增加班组派工显示
                strFilter = String.Format("containerid='{0}' and specid='{1}' and workflowstepid='{2}'", row("containerid").ToString().Trim(), stepRows(i)("specid").ToString().Trim(), stepRows(i)("workflowstepid").ToString().Trim())
                Dim teamRows As DataRow() = specTable.Select(strFilter)

                strFilter = String.Format("containerid='{0}' and workflowstepid='{1}'", row("containerid").ToString().Trim(), stepRows(i)("workflowstepid").ToString().Trim())
                If synerRows.Length = 0 Then '按workflowstepid再筛选一遍
                    synerRows = synerTable.Select(strFilter)
                End If
                Dim specCheckRows As DataRow() = speCheckTable.Select(strFilter)

                Dim strSql2 = String.Format("SELECT * FROM containernonconforminginfo cni WHERE cni.containerid = '{0}' AND cni.workflowstepid = '{1}'", row("containerid").ToString(), stepRows(i)("workflowstepid").ToString())
                Dim qualityRecordTable As DataTable = dal.GetCheckTempInfo(strSql2)

                '筛选外委数据 add:Wangjh 20171101
                Dim commissionRows As DataRow() = commissionDt.Select(strFilter)

                If specCheckRows.Length > 0 Then
                    rowTmp("A" & (i + 1).ToString()) = "4" '已检验
                    If qualityRecordTable.Rows.Count > 0 Then
                        rowTmp("A" & (i + 1).ToString()) = "5" '有问题
                    End If
                    '完工
                    If specCheckRows(0)("usecompleted") = "1" Then
                        rowTmp("A" & (i + 1).ToString()) = "6" '已完工
                    End If
                    '
                Else
                    If dispatRows.Length > 0 Then
                        rowTmp("A" & (i + 1).ToString()) = "2" '已派工
                        For Each tmpRow As DataRow In dispatRows
                            If Convert.ToInt32(tmpRow("reportedqty")) > 0 Then
                                rowTmp("A" & (i + 1).ToString()) = "3" '已报工
                                '查询是否有待检验检验，待处理数据 add:Wangjh 20181016
                                Dim qStr As String = String.Format("SELECT * FROM containerchecksuspending ccs where ccs.containerid='{0}' AND ccs.workflowstepid='{1}' AND ccs.isuse=0",
                                                                   row("containerid").ToString().Trim(), stepRows(i)("WorkFlowStepID").ToString().Trim())
                                If DatabaseHelper.Query_ReturnDatTable(qStr).Rows.Count > 0 Then
                                    rowTmp("A" & (i + 1).ToString()) = "14" '已报工待处理状态
                                End If
                                '查寻：进行过不合格审理并未检验
                                qStr = "  SELECT cnon.containernonconforminginfoid qualityrecordid FROM containernonconforminginfo cnon "
                                qStr += String.Format(" WHERE cnon.containerid='{0}' AND cnon.workflowstepid='{1}' AND cnon.checkemployeeid IS NULL", row("containerid").ToString().Trim(), stepRows(i)("WorkFlowStepID").ToString().Trim())
                                Dim qulaityRecord As DataTable = dal.GetCheckTempInfo(qStr)
                                If qulaityRecord.Rows.Count > 0 Then
                                    rowTmp("A" & (i + 1).ToString()) = "9" '已报工待审理状态
                                End If
                                Exit For
                            End If
                        Next
                    ElseIf String.IsNullOrWhiteSpace(Convert.ToString(rowTmp("A" & (i + 1)))) Then '之前赋值，不能再重新赋值为”1“ add by tianFW 2019年1月10日
                        rowTmp("A" & (i + 1).ToString()) = "1" '未开始
                        'Add by Wzj 2018/1/5 增加班组派工显示
                        If (teamRows.Length > 0) Then
                            If teamRows(0).IsNull("teamname") = False Then
                                rowTmp("A" & (i + 1).ToString()) = String.Format("1;;;{0}", teamRows(0)("teamname").ToString)
                            End If
                        End If
                    End If
                End If
                If synerRows.Length > 0 Then
                    If synerRows(0)("senddate").ToString = "JX" Then 'add:Wangjh 2017-8-24 机匣的情况下
                        If synerRows(0).IsNull("returndate") = True Then
                            rowTmp("A" & (i + 1).ToString()) = "7" '转出
                        Else
                            rowTmp("A" & (i + 1).ToString()) = "8" '转入
                        End If
                    Else ' add:Wangjh 2017-8-24 结构件的情况下 
                        If synerRows(0).IsNull("returndate") = False Then
                            rowTmp("A" & (i + 1).ToString()) = "8" '转入
                        ElseIf synerRows(0).IsNull("completedate") = False Then
                            rowTmp("A" & (i + 1).ToString()) = "12" '完成
                        ElseIf synerRows(0).IsNull("toacceptdate") = False Then
                            rowTmp("A" & (i + 1).ToString()) = "13" '接收
                        ElseIf synerRows(0).IsNull("senddate") = False Then
                            rowTmp("A" & (i + 1).ToString()) = "7" '转出
                        End If
                    End If
                End If
                '有外委情况
                If commissionRows.Length > 0 Then
                    If String.IsNullOrWhiteSpace(commissionRows(0)("indate").ToString) And Not String.IsNullOrWhiteSpace(commissionRows(0)("outdate").ToString) Then '外委转出
                        rowTmp("A" & (i + 1).ToString()) = "10"
                    ElseIf Not String.IsNullOrWhiteSpace(commissionRows(0)("indate").ToString) Then '外委转回
                        rowTmp("A" & (i + 1).ToString()) = "11"
                    End If
                End If
            Next
            returnTable.Rows.Add(rowTmp)
        Next
        result.DBTable = returnTable.Copy()
        result.DBDataSet.Tables.Add(machineWorkflowDt)
        result.Result = True
        Return result

    End Function

    ''' <summary>
    ''' 查询批次所有加工的工艺
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAllMachineWorkflowByContainer(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim machineSql As String = <string>
                                SELECT csr.workflowname,csr.workflowid,cci.workflowid cuworkflowid  FROM containersteprecord csr
                                 LEFT JOIN containercurrentinfo cci on cci.containerid=csr.containerid
                                WHERE 1=1
                                   </string>.Value
        machineSql += String.Format(" and c.containername LIKE '%{0}%' and csr.factoryid like '%{1}%'", para("ContainerName"), para("FactoryID"))
        Dim shipSql As String = <string>
                            SELECT wb.workflowname,w.workflowid,null cuworkflowid FROM containershiprecord csh 
                            LEFT JOIN container c ON c.containerid=csh.containerid
                            LEFT JOIN Workflowstep ws ON ws.workflowstepid=csh.workflowstepid
                            LEFT JOIN workflow w ON w.workflowid=ws.workflowid
                            LEFT JOIN Workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
                            WHERE c.status=1 and csh.isuse=0
                                </string>
        Dim commSql As String = <string>
                            SELECT wb.workflowname,w.workflowid,null cuworkflowid  FROM CONTAINERCOMMISSIONING CMM
                            LEFT JOIN container c ON c.containerid=cmm.containerid
                            LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CMM.WORKFLOWSTEPID
                            LEFT JOIN workflow w ON w.workflowid=ws.workflowid
                            LEFT JOIN Workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
                            WHERE c.status=1 and cmm.isuse=0
                                </string>
        Dim condition As String = String.Format(" and c.containername LIKE '%{0}%' and w.factoryid like '%{1}%'", para("ContainerName"), para("FactoryID"))
        Dim sql As String = machineSql + "UNION" + shipSql + condition + "UNION" + commSql + condition
        Return DatabaseHelper.Query_ReturnDatTable(sql)
    End Function

    ''' <summary>
    ''' 零件进度表主数据查询
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMainData(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult

        Dim strWorkCenterName As String = ""
        If QueryCondition.Keys.Contains("WorkCenterName") = True Then
            If QueryCondition("WorkCenterName") IsNot Nothing Then
                strWorkCenterName = QueryCondition("WorkCenterName").ToString()
            End If
        End If

        Dim strContainerName As String = ""
        If QueryCondition.Keys.Contains("ContainerName") = True Then
            strContainerName = QueryCondition("ContainerName").ToString()
        End If

        Dim strProductName As String = ""
        If QueryCondition.Keys.Contains("productname") = True Then
            strProductName = QueryCondition("productname").ToString()
        End If

        Dim strProductFamilyname As String = ""
        If QueryCondition.Keys.Contains("ProductFamilyName") = True Then
            strProductFamilyname = QueryCondition("ProductFamilyName").ToString()
        End If

        Dim strProductDesc As String = ""
        If QueryCondition.Keys.Contains("productdesp") = True Then
            strProductDesc = QueryCondition("productdesp").ToString()
        End If

        'strFactoryName = ""
        'If QueryCondition.Keys.Contains("FactoryName") = True Then
        '    strFactoryName = QueryCondition("FactoryName").ToString()
        'End If
        'strCreateEmployeeName = ""
        'If QueryCondition.Keys.Contains("CreateEmployeeName") = True Then
        '    strCreateEmployeeName = QueryCondition("CreateEmployeeName").ToString()
        'End If
        'If QueryCondition.Keys.Contains("DispClose") = True Then
        '    boolDispClose = QueryCondition("DispClose")
        'End If

        Dim strStartDate As String = ""
        If QueryCondition.Keys.Contains("StartDate") = True Then
            If QueryCondition("StartDate") <> Nothing Then
                If QueryCondition("StartDate").ToString().Trim() <> "" Then
                    strStartDate = QueryCondition("StartDate").ToString()
                End If
            End If
        End If

        Dim strEndDate As String = ""
        If QueryCondition.Keys.Contains("EndDate") = True Then
            If QueryCondition("EndDate") <> Nothing Then
                If QueryCondition("EndDate").ToString().Trim() <> "" Then
                    strEndDate = QueryCondition("EndDate").ToString()
                End If
            End If
        End If
        'add:Wangjh 增加结构件转工判断
        Dim strSql As String = <string>SELECT DECODE(csr.containershiprecordid,NULL,0,1) isship,csr.tofactoryid,csr.containershiprecordid,container.containerid, container.plannedcompletiondate,Container.Containername, ccu.Productname, Product.Description, Factory.Factoryname, Container.Qty, ccu.Mfgordername,
'' as Employeename,Container.Factorystartdate,container.originalstartdate,null cdoname,productfamily.productfamilyname,ccu.workflowname,workcenter.workcentername,ccu.workflowstepid cuworkflowstepid,ccu.isinstore,
ccu.workflowid,csi2.sequence SPLITSequence,ccu.ishold FROM Container 
LEFT JOIN ContainercurrentInfo ccu ON Container.ContainerID = ccu.ContainerID 
LEFT JOIN Product ON Container.Productid = Product.Productid 
LEFT JOIN productfamily on productfamily.productfamilyid=Product.Productfamilyid  
LEFT JOIN Factory ON ccu.originalfactoryid = Factory.Factoryid 
LEFT JOIN workcenter ON workcenter.workcenterid = Container.Workcenterid 
 LEFT JOIN containershiprecord csr ON csr.containershiprecordid=ccu.shipid AND csr.toacceptor IS NOT null AND csr.isuse=0 
 LEFT JOIN containershiprecive csre ON csre.containershiprecordid=csr.containershiprecordid AND csre.isuse=0
LEFT JOIN containersplitinfo csi2 ON  csi2.containerid=Container.splitfromid AND csi2.splitcontainername=Container.containername AND csi2.splitresult=0 and csi2.isuse=0--当前卡为分出来的卡 
WHERE  Container.Parentcontainerid Is NULL And Container.Containerid != Container.Containername AND Container.status=1 </string>.Value
        If strContainerName <> "" Then
            strSql += " AND Container.Containername LIKE '%" & strContainerName & "%'" & vbCrLf
        End If
        If strProductFamilyname <> "" Then
            strSql += " AND Productfamily.Productfamilyname = '" & strProductFamilyname & "'" & vbCrLf
        End If
        If strProductName <> "" Then
            strSql += " AND ccu.Productname LIKE '%" & strProductName & "%'" & vbCrLf
        End If

        'add zhunan 2022.6.22
        If QueryCondition.ContainsKey("instorestatus") Then
            If QueryCondition("instorestatus").ToString() = "在制" Then
                strSql += " AND ccu.isinstore = 0 " & vbCrLf
            Else
                strSql += " AND ccu.isinstore = 1 " & vbCrLf
            End If
        End If

        'add zhunan  2022.8.16 扫描流水卡
        If QueryCondition.ContainsKey("ScanContainerName") Then
            If QueryCondition("ScanContainerName").ToString() <> "" Then
                strSql += String.Format(" AND ccu.containername = '{0}'", QueryCondition("ScanContainerName"))
            End If
        End If

        If strProductDesc <> "" Then
            strSql += " AND Product.Description LIKE '%" & strProductDesc & "%'" & vbCrLf
        End If

        If strWorkCenterName <> "" Then
            strSql += " AND workcenter.workcentername= '" & strWorkCenterName & "'" & vbCrLf
        End If

        If strStartDate <> "" Then
            strSql += "AND Container.originalstartdate >= TO_DATE('" & strStartDate & "','yyyy-MM-dd') " & vbCrLf
        End If
        If strEndDate <> "" Then
            strSql += "AND Container.originalstartdate <= TO_DATE('" & strEndDate & "','yyyy-MM-dd') " & vbCrLf
        End If
        If QueryCondition.ContainsKey("MfgOrderName") Then 'ADD:Wangjh 2017-8-16
            strSql += String.Format(" AND ccu.MfgOrderName LIKE '%{0}%'", QueryCondition("MfgOrderName"))
        End If
        If QueryCondition.ContainsKey("FactoryID") Then 'ADD:Wangjh 2017-8-16
            strSql += String.Format(" AND (CONTAINER.ORIGINALFACTORYID='{0}' OR (csr.tofactoryid='{1}' and csre.reciver IS NULL))", QueryCondition("FactoryID"), QueryCondition("FactoryID"))
        End If
        strSql += <string> group by csr.containershiprecordid,csr.tofactoryid,csr.containershiprecordid,container.containerid,container.plannedcompletiondate,Container.Containername, ccu.Productname, Product.Description, Factory.Factoryname, 
 Container.Qty, ccu.Mfgordername,Container.Factorystartdate,container.originalstartdate,productfamily.productfamilyname,ccu.workflowname,workcenter.workcentername,ccu.workflowstepid,
 ccu.workflowid,ccu.ishold,csi2.sequence,ccu.isinstore</string>.Value
        strSql += " order by ccu.isinstore,container.containerid desc "
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = strSql

        Return dal.RunProcedureForPage(paraPage) '主数据查询完毕
    End Function

    ''' <summary>
    ''' 批次工序处理
    ''' </summary>
    ''' <param name="strContainerList"></param>
    ''' <returns></returns>
    ''' <remarks>增加对批次工序是否子工艺以及是否加工状态查询 add by tianFW 2018年12月26日</remarks>
    Function GetStepTable(ByVal strContainerList As String, ByVal containerAndWorkflow As DataTable, ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSql As String = ""
        Dim strSql2 As String = "SELECT c.containername,c.qty, wp1.workflowstepid, wp1.workflowstepname,wp1.sequence,s.specid,t.teamname,c.containerid,css.noship,wp1.wipmsglabel" & vbCrLf & _
          "FROM Container c " & vbCrLf & _
          "LEFT JOIN currentstatus cu ON cu.currentstatusid=c.currentstatusid " & vbCrLf & _
          "LEFT JOIN workflowstep wp ON wp.workflowstepid=cu.workflowstepid " & vbCrLf & _
          "LEFT JOIN workflowstep wp1 ON wp1.workflowid=wp.workflowid " & vbCrLf & _
          "LEFT JOIN specbase sb ON sb.specbaseid=wp1.specbaseid " & vbCrLf & _
          "LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,wp1.specid)" &
          "left join containerteamdispatch ctd on ctd.containerid=c.containerid AND CTD.WORKFLOWSTEPID = WP1.WORKFLOWSTEPID" & vbCrLf & _
          "left join team t on t.teamid=ctd.teamid" & vbCrLf & _
          "LEFT JOIN containerstepnoship css ON css.containerid = c.containerid AND css.workflowstepid = wp1.workflowstepid" & vbCrLf & _
          "WHERE c.containername IN (" & strContainerList & ")  AND wp.sequence IS NOT NULL AND wp1.sequence IS NOT NULL " & vbCrLf & _
          "group by c.containername,c.qty, wp1.workflowstepid, wp1.workflowstepname,wp1.sequence,s.specid,t.teamname,c.containerid,css.noship,wp1.wipmsglabel " & vbCrLf & _
          "ORDER BY c.containername, wp1.sequence "

        If para.ContainsKey("WorkflowID") Then
            containerAndWorkflow.DefaultView.RowFilter = "WorkflowID='" + para("WorkflowID") + "'"
            containerAndWorkflow = containerAndWorkflow.DefaultView.ToTable
            containerAndWorkflow = containerAndWorkflow.DefaultView.ToTable(True, "ContainerID")
            containerAndWorkflow.AcceptChanges()
            strSql = "SELECT ws.workflowstepid,ws.workflowstepname,ws.sequence,s.specid,ws.wipmsglabel FROM workflowstep ws" &
                 " LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid" &
                 " LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)" &
                 " where ws.workflowid='" + para("WorkflowID") + "'" &
                 " AND ws.sequence IS NOT NULL "
            Dim stepDt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

            Dim stepIDs As String = ""
            For i As Integer = 0 To stepDt.Rows.Count - 1
                If i = stepDt.Rows.Count - 1 Then
                    stepIDs += String.Format("ctd.workflowstepid='{0}'", stepDt.Rows(i)("workflowstepid"))
                Else
                    stepIDs += String.Format("ctd.workflowstepid='{0}' or ", stepDt.Rows(i)("workflowstepid"))
                End If
            Next
            Dim resultDt As New DataTable
            For i As Integer = 0 To containerAndWorkflow.Rows.Count - 1
                strSql = "SELECT c.containername,c.containerid,c.qty,t.teamname,ws.workflowstepid,ws.workflowstepname,ws.sequence,s.specid,css.noship,ws.wipmsglabel" & vbCrLf & _
                  " FROM container c" & vbCrLf & _
                  " LEFT JOIN containerteamdispatch ctd ON ctd.containerid=c.containerid AND (" + stepIDs + ")" & vbCrLf & _
                  " LEFT JOIN workflowstep ws ON ws.workflowstepid=ctd.workflowstepid and ws.sequence IS NOT NULL" & vbCrLf & _
                  " LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid" & vbCrLf & _
                  " LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)" & vbCrLf & _
                  " LEFT JOIN team t ON t.teamid=ctd.teamid" & vbCrLf & _
                  " LEFT JOIN containerstepnoship css ON css.containerid = c.containerid AND css.workflowstepid = ws.workflowstepid"
                strSql += " WHERE c.containerid='"
                strSql += containerAndWorkflow.Rows(i)("ContainerID") + "'"
                Dim teamSpec As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
                Dim temDt As New DataTable '= teamSpec.Clone
                If teamSpec.Rows.Count < stepDt.Rows.Count And teamSpec.Rows.Count > 0 Then
                    temDt.Columns.Add("containername").DefaultValue = teamSpec.Rows(0)("containername")
                    temDt.Columns.Add("containerid").DefaultValue = teamSpec.Rows(0)("containerid")
                    temDt.Columns.Add("qty").DefaultValue = teamSpec.Rows(0)("qty")
                    temDt.Columns.Add("teamname")
                    temDt.Columns.Add("noship")
                    temDt.Rows.Add()
                    temDt.Merge(stepDt)
                Else
                    temDt = teamSpec.Copy
                End If
                temDt.DefaultView.RowFilter = "workflowstepid is not null"
                temDt = temDt.DefaultView.ToTable()
                temDt.AcceptChanges()
                If i = 0 Then
                    resultDt = temDt
                Else
                    resultDt.Merge(temDt)
                End If
            Next
            If resultDt.Rows.Count = 0 Then
                Return DatabaseHelper.Query_ReturnDatTable(strSql2)
            Else
                Return resultDt
            End If
        Else
            '查询主数据批次工艺路线所有工序
            Return DatabaseHelper.Query_ReturnDatTable(strSql2)
        End If

    End Function

    ''' <summary>
    ''' 合并两个表，筛选重复列，重复行
    ''' add:Wangjh
    ''' </summary>
    ''' <param name="dt1"></param>
    ''' <param name="dt2"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CombineTable(ByVal dt1 As DataTable, ByVal dt2 As DataTable) As DataTable
        dt1 = dt1.DefaultView.ToTable(True)
        dt2 = dt2.DefaultView.ToTable(True)
        Dim dt As New DataTable
        For Each colname As DataColumn In dt1.Columns
            If dt.Columns.Contains(colname.ToString) Then
                Continue For
            End If
            dt.Columns.Add(colname.ToString)
        Next
        For Each colname As DataColumn In dt2.Columns
            If dt.Columns.Contains(colname.ToString) Then
                Continue For
            End If
            dt.Columns.Add(colname.ToString)
        Next
        '取最大的行数
        Dim n As Integer = dt1.Rows.Count
        If n < dt2.Rows.Count Then
            n = dt2.Rows.Count
        End If
        '循环赋值
        For i As Integer = 0 To n - 1
            If i < dt1.Rows.Count Then
                dt.ImportRow(dt1.Rows(i))
            End If
            If i < dt2.Rows.Count Then
                For Each colname As DataColumn In dt2.Columns
                    If dt.Rows(i).IsNull(colname.ToString) Then
                        dt.Rows(i)(colname.ToString) = dt2.Rows(i)(colname.ToString)
                    End If
                Next
            End If
        Next
        dt.AcceptChanges()
        Return dt
    End Function
#End Region

#Region "零件进度表，工步级信息展示"
    ''' <summary>
    ''' 获取批次工步加工记录
    ''' add:Wangjh 20220920
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerStepinfoWorkRecord(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMESContainerReportIBusiness.GetContainerStepinfoWorkRecord
        Dim strSql = <string>select si.stepno,si.description StepInfoDesc,csd.createdate DispatchDate,e.fullname DispatchFullName,
csd.qty DispatchQty,wm_concat(e2.fullname) dispatchworkname,csd.containerstepdispatchid,csw.createdate ReportDate,
rd.description ResourceDesc,e3.fullname ReportEmployeeName,t.teamname ,csw.qty ReportQty,cci.createdate CheckDate,
e4.fullname CheckEmployeeName,cci.acceptanceqty2 PassQty,cci.lossqty  from containerstepdispatch2 csd
left join containerstepdispatchdetail2 csdd on csdd.containerstepdispatchid=csd.containerstepdispatchid
left join employee e on e.employeeid=csd.employeeid--派工人
left join employee e2 on e2.employeeid=nvl(csd.workerid,csdd.workerid)--派给工人
left join stepinfo si on si.stepinfoid=csd.stepinfoid
left join resourcedef rd on rd.resourceid=csd.resourceid
left join containerstepworkreport2 csw on csw.containerstepdispatchid=csd.containerstepdispatchid and csw.isuse=0
left join employee e3 on e3.employeeid=csw.workerid
left join team t on t.teamid=csw.teamid
left join containercheckinfo2 cci on cci.containerstepworkreportid=csw.containerstepworkreportid and cci.isuse=0
left join employee e4 on e4.employeeid=cci.checkerid 
where csd.isuse=0 </string>.Value
        If para.ContainsKey("ContainerID") Then
            strSql += String.Format(" and csd.containerid='{0}' ", para("ContainerID"))
        End If
        If para.ContainsKey("WorkFlowStepID") Then
            strSql += String.Format(" and csd.WorkflowStepId='{0}' ", para("WorkFlowStepID"))
        End If
        strSql += <string>group by si.stepno,si.description ,csd.createdate ,e.fullname ,csd.qty,csd.containerstepdispatchid,csw.createdate,
rd.description,e3.fullname,t.teamname,csw.qty,cci.createdate,e4.fullname ,cci.acceptanceqty2 ,cci.lossqty,si.stepseq</string>.Value
        strSql += " order by si.stepseq "
        Return DatabaseHelper.Query_ReturnDatTable(strSql)

    End Function
#End Region


    ''' <summary>
    ''' 获取零件进度表主界面数据
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerReportDetailInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetContainerReportDetailInfo
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerName, strProductName, strProductFamilyname, strProductDesc, strFactoryName, strStartDate, strEndDate, strCreateEmployeeName, strWorkCenterName As String
            Dim boolDispClose As Boolean = False
            strContainerName = ""
            If QueryCondition.Keys.Contains("ContainerName") = True Then
                strContainerName = QueryCondition("ContainerName").ToString()
            End If
            strProductName = ""
            If QueryCondition.Keys.Contains("ProductName") = True Then
                strProductName = QueryCondition("ProductName").ToString()
            End If

            strProductFamilyname = ""
            If QueryCondition.Keys.Contains("ProductFamilyName") = True Then
                strProductFamilyname = QueryCondition("ProductFamilyName").ToString()
            End If

            strProductDesc = ""
            If QueryCondition.Keys.Contains("ProductDesc") = True Then
                strProductDesc = QueryCondition("ProductDesc").ToString()
            End If

            strFactoryName = ""
            If QueryCondition.Keys.Contains("FactoryName") = True Then
                strFactoryName = QueryCondition("FactoryName").ToString()
            End If
            strCreateEmployeeName = ""
            If QueryCondition.Keys.Contains("CreateEmployeeName") = True Then
                strCreateEmployeeName = QueryCondition("CreateEmployeeName").ToString()
            End If
            If QueryCondition.Keys.Contains("DispClose") = True Then
                boolDispClose = QueryCondition("DispClose")
            End If

            strStartDate = ""
            If QueryCondition.Keys.Contains("StartDate") = True Then
                If QueryCondition("StartDate") <> Nothing Then
                    If QueryCondition("StartDate").ToString().Trim() <> "" Then
                        strStartDate = QueryCondition("StartDate").ToString()
                    End If
                End If
            End If

            strEndDate = ""
            If QueryCondition.Keys.Contains("EndDate") = True Then
                If QueryCondition("EndDate") <> Nothing Then
                    If QueryCondition("EndDate").ToString().Trim() <> "" Then
                        strEndDate = QueryCondition("EndDate").ToString()
                    End If
                End If
            End If

            strWorkCenterName = ""
            If QueryCondition.Keys.Contains("WorkCenterName") = True Then
                If QueryCondition("WorkCenterName") IsNot Nothing Then
                    strWorkCenterName = QueryCondition("WorkCenterName").ToString()
                End If
            End If

            strSQL = "SELECT Rownum, Cinfo.* FROM" & vbCrLf & _
                    "(SELECT distinct Container.Containername, Productbase.Productname, Product.Description, " & vbCrLf & _
                    "       Factory.Factoryname, Container.Qty, Mfgorder.Mfgordername," & vbCrLf & _
                    "       DECODE(Inventtaskinfo.Urgent,'0','普通','1','紧急','2','特急') AS Urgent, " & vbCrLf & _
                    "       (Round(NVL(CMove.ComStepNum, 0) / NVL(CStepAll.AllStepNum, 1) *100 , 0) || '%' ) AS ComPercent," & vbCrLf & _
                    "       Mfgorder.Plannedcompletiondate, Container.Containerid,  Orderstatus.Orderstatusname, " & vbCrLf & _
                    "       (CASE WHEN Cdodefinition.Cdoname = 'MachineMfgWorkFlow' THEN '1' ELSE '0' END) AS Materielstate, '' as Employeename,Container.Factorystartdate,container.originalstartdate" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "LEFT JOIN Product ON Container.Productid = Product.Productid" & vbCrLf & _
                    "LEFT JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid" & vbCrLf & _
                    "LEFT JOIN productfamily on productfamily.productfamilyid=Product.Productfamilyid" & vbCrLf & _
                    "LEFT OUTER JOIN Mfgorder ON Mfgorder.Mfgorderid = Container.Mfgorderid" & vbCrLf & _
                    "LEFT OUTER JOIN Inventtaskinfo ON Mfgorder.Inventtaskinfoid = Inventtaskinfo.Inventtaskinfoid" & vbCrLf & _
                    "LEFT JOIN Currentstatus ON  Currentstatus.Currentstatusid = Container.Currentstatusid" & vbCrLf & _
                    "LEFT OUTER JOIN Factory ON Currentstatus.Factoryid = Factory.Factoryid " & vbCrLf & _
                    "LEFT JOIN workcenter ON workcenter.workcenterid = Container.Workcenterid " & vbCrLf & _
                    "LEFT OUTER JOIN Orderstatus ON Mfgorder.Orderstatusid = Orderstatus.Orderstatusid" & vbCrLf & _
                    "LEFT JOIN Workflowstep ON Workflowstep.Workflowstepid = Currentstatus.Workflowstepid" & vbCrLf & _
                    "LEFT JOIN Workflow ON Workflow.Workflowid = WorkflowStep.Workflowid " & _
                    "LEFT JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid " & _
                    "LEFT JOIN Historymainline ON Historymainline.Containerid = Container.Containerid" & vbCrLf & _
                    "LEFT JOIN Cdodefinition Cdodefinition2 ON Cdodefinition2.Cdodefid = Historymainline.Txntype" & vbCrLf & _
                    "LEFT JOIN Employee ON Employee.Employeeid = Historymainline.Employeeid" & vbCrLf & _
                    "LEFT OUTER JOIN " & vbCrLf & _
                    "(SELECT CMoveTemp.Containername, COUNT(CMoveTemp.Workflowstepid) AS ComStepNum" & vbCrLf & _
                    "FROM" & vbCrLf & _
                    "(SELECT DISTINCT Container.Containername,  Historymainline.Workflowstepid" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "LEFT JOIN Historymainline ON Container.Containerid = Historymainline.Containerid" & vbCrLf & _
                    "INNER JOIN Speclastcheckhistory ON Speclastcheckhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "WHERE Historymainline.Status = 1" & vbCrLf & _
                    ") CMoveTemp" & vbCrLf & _
                    "GROUP BY CMoveTemp.Containername ) CMove" & vbCrLf & _
                    "ON CMove.Containername = Container.Containername" & vbCrLf & _
                    "LEFT OUTER JOIN " & vbCrLf & _
                    "(" & vbCrLf & _
                    "SELECT CStepAllTemp.Containername, COUNT( CStepAllTemp.Workflowstepname) AS AllStepNum" & vbCrLf & _
                    "FROM" & vbCrLf & _
                    "(SELECT Container.Containername, WorkflowstepAll.Workflowstepname" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid" & vbCrLf & _
                    "INNER JOIN Workflowstep ON Workflowstep.Workflowstepid = Currentstatus.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Workflowstep WorkflowstepAll ON WorkflowstepAll.Workflowid = Workflowstep.Workflowid " & vbCrLf & _
                    "WHERE  Container.Parentcontainerid IS NULL" & vbCrLf & _
                    ") CStepAllTemp" & vbCrLf & _
                    "GROUP BY CStepAllTemp.Containername) CStepAll" & vbCrLf & _
                    "ON CStepAll.Containername = Container.Containername" & vbCrLf & _
                    "WHERE Container.Parentcontainerid IS NULL AND Container.Qty >= 0 " & vbCrLf & _
                    "AND (Cdodefinition.Cdoname = 'MachineMfgWorkFlow' OR Container.Mfgworkflowid IS NOT NULL) AND Container.status=1 "

            If strContainerName <> "" Then
                strSQL += " AND Container.Containername LIKE '%" & strContainerName & "%'" & vbCrLf
            End If
            If strProductFamilyname <> "" Then
                strSQL += " AND Productfamily.Productfamilyname = '" & strProductFamilyname & "'" & vbCrLf
            End If
            If strProductName <> "" Then
                strSQL += " AND Productbase.Productname LIKE '%" & strProductName & "%'" & vbCrLf
            End If
            If strProductDesc <> "" Then
                strSQL += " AND Product.Description LIKE '%" & strProductDesc & "%'" & vbCrLf
            End If
            If strFactoryName <> "" Then
                strSQL += " AND Factory.FactoryName ='" & strFactoryName & "'" & vbCrLf
            End If
            If boolDispClose = False Then
                strSQL += " AND Container.status=1 " & vbCrLf
            End If
            If strCreateEmployeeName <> "" Then
                strSQL += " AND UPPER(Employee.Employeename) =UPPER('" & strCreateEmployeeName & "') " & vbCrLf
            End If
            If strWorkCenterName <> "" Then
                strSQL += " AND workcenter.workcentername  = '" & strWorkCenterName & "' " & vbCrLf
            End If
            If strStartDate <> "" Then
                strSQL += "AND Container.originalstartdate >= TO_DATE('" & strStartDate & "','yyyy-MM-dd') " & vbCrLf
            End If
            If strEndDate <> "" Then
                strSQL += "AND Container.originalstartdate <= TO_DATE('" & strEndDate & "','yyyy-MM-dd') " & vbCrLf
            End If
            strSQL = strSQL & "ORDER BY container.Factorystartdate,container.originalstartdate DESC) Cinfo  order by Cinfo.Factorystartdate desc" & vbCrLf

            Dim dtMain As DataTable = dal.GetCheckTempInfo(strSQL)

            strSQL = "SELECT distinct CFin.Containername, CFin.Workflowstepname, CFin.Specname, CFin.Containerid, CFin.Specid, CFin.Sequence," & vbCrLf & _
                    "       CFin.SpecReportStatus," & vbCrLf & _
                    "       CFin.QualityrecordStatus, CFin.IsMain, CFin.CheckCom,CFin.LastSpecid, CFin.Specid " & vbCrLf & _
                    "FROM " & vbCrLf & _
                    "( SELECT Cinfo.Containername, Cinfo.Workflowstepname, Cinfo.Specname, Cinfo.Containerid, Cinfo.Specid, Cinfo.Sequence," & vbCrLf & _
                    "       (CASE WHEN CLastMove.Specid IS NOT NULL THEN 3 " & vbCrLf & _
                    "           ELSE (CASE WHEN NVL(CReport.RQty, 0) >= Cinfo.Qty THEN 2 " & vbCrLf & _
                    "               ELSE (CASE WHEN (CReport.RQty IS NULL AND CChange.Containername IS NULL) THEN 0 " & vbCrLf & _
                    "                  ELSE (CASE WHEN (CChange.ckZGStatus = 1) THEN 2 " & vbCrLf & _
                    "                       ELSE (CASE WHEN (CChange.ckZGStatus = 1) THEN 2 ELSE 1 END ) END )" & vbCrLf & _
                    "                  END) " & vbCrLf & _
                    "            END) " & vbCrLf & _
                    "       END ) AS SpecReportStatus," & vbCrLf & _
                    "       (CASE WHEN (CQualityrecord.Containername IS NULL ) THEN 0 " & vbCrLf & _
                    "             ELSE 1 " & vbCrLf & _
                    "       END ) AS QualityrecordStatus, '1' AS IsMain, CLastMove.Specid as CheckCom,CLastMove.Specid as LastSpecid" & vbCrLf & _
                    "FROM " & vbCrLf & _
                    "(" & vbCrLf & _
                    "SELECT Container.Containername,Container.Qty, CStep.Workflowstepname, CStep.Sequence,Specbase.Specname, Container.Containerid, Spec.Specid, Employee.Employeename" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "INNER JOIN Product ON Container.Productid = Product.Productid" & vbCrLf & _
                    "INNER JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid" & vbCrLf & _
                    "LEFT OUTER JOIN Mfgorder ON Mfgorder.Mfgorderid = Container.Mfgorderid" & vbCrLf & _
                    "LEFT OUTER JOIN ProductType ON ProductType.ProductTypeid = Product.ProductTypeid" & vbCrLf & _
                    "LEFT OUTER JOIN Productfamily ON Productfamily.Productfamilyid = Product.Productfamilyid" & vbCrLf & _
                    "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid" & vbCrLf & _
                    "LEFT OUTER JOIN Factory ON Currentstatus.Factoryid = Factory.Factoryid " & vbCrLf & _
                    "INNER JOIN Workflowstep ON Workflowstep.Workflowstepid = Currentstatus.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = WorkflowStep.Workflowid " & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid " & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(" & vbCrLf & _
                    "SELECT Workflowstep.Workflowid, Workflowstep.Workflowstepid, Workflowstep.Workflowstepname,Workflowstep.Sequence, (CASE WHEN Workflowstep.Specid = '0000000000000000' THEN Specbase.Revofrcdid ELSE Workflowstep.Specid END) AS Specid" & vbCrLf & _
                    "FROM Workflowstep" & vbCrLf & _
                    "LEFT JOIN Specbase ON Workflowstep.Specbaseid = Specbase.Specbaseid " & vbCrLf & _
                    ") CStep" & vbCrLf & _
                    "ON CStep.Workflowid = Workflowstep.Workflowid" & vbCrLf & _
                    "INNER JOIN Spec ON CStep.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                    "INNER JOIN Historymainline ON Historymainline.Containerid = Container.Containerid" & vbCrLf & _
                    "INNER JOIN Cdodefinition Cdodefinition2 ON Cdodefinition2.Cdodefid = Historymainline.Txntype" & vbCrLf & _
                    "INNER JOIN Employee ON Employee.Employeeid = Historymainline.Employeeid" & vbCrLf & _
                    "WHERE Container.Parentcontainerid IS NULL AND Container.Qty >= 0 " & vbCrLf & _
                    "AND Cdodefinition.Cdoname = 'MachineMfgWorkFlow' " & vbCrLf & _
                    " AND Container.status=1 " & vbCrLf

            If strContainerName <> "" Then
                strSQL += " AND Container.Containername LIKE '%" & strContainerName & "%'" & vbCrLf
            End If
            If strProductFamilyname <> "" Then
                strSQL += " AND Productfamily.Productfamilyname = '" & strProductFamilyname & "'" & vbCrLf
            End If
            If strProductName <> "" Then
                strSQL += " AND Productbase.Productname LIKE '%" & strProductName & "%'" & vbCrLf
            End If
            If strProductDesc <> "" Then
                strSQL += " AND Product.Description LIKE '%" & strProductDesc & "%'" & vbCrLf
            End If
            If strFactoryName <> "" Then
                strSQL += " AND Factory.FactoryName ='" & strFactoryName & "'" & vbCrLf
            End If
            If boolDispClose = False Then
                strSQL += " AND Container.status=1 " & vbCrLf
            End If
            If strStartDate <> "" Then
                strSQL += "AND Container.originalstartdate >= TO_DATE('" & strStartDate & "','yyyy-MM-dd') " & vbCrLf
            End If
            If strEndDate <> "" Then
                strSQL += "AND Container.originalstartdate <= TO_DATE('" & strEndDate & "','yyyy-MM-dd') " & vbCrLf
            End If
            If strCreateEmployeeName <> "" Then
                strSQL += " AND UPPER(Employee.Employeename) =UPPER('" & strCreateEmployeeName & "') " & vbCrLf
            End If
            strSQL = strSQL & "UNION ALL" & vbCrLf & _
                    "SELECT Container.Containername,Container.Qty, CStep.Workflowstepname, CStep.Sequence,Specbase.Specname, Container.Containerid, Spec.Specid, Employee.Employeename" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "INNER JOIN Product ON Container.Productid = Product.Productid" & vbCrLf & _
                    "INNER JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid" & vbCrLf & _
                    "LEFT OUTER JOIN Mfgorder ON Mfgorder.Mfgorderid = Container.Mfgorderid" & vbCrLf & _
                    "LEFT OUTER JOIN ProductType ON ProductType.ProductTypeid = Product.ProductTypeid" & vbCrLf & _
                    "LEFT OUTER JOIN Productfamily ON Productfamily.Productfamilyid = Product.Productfamilyid" & vbCrLf & _
                    "INNER JOIN Currentstatus ON Currentstatus.Currentstatusid = Container.Currentstatusid" & vbCrLf & _
                    "LEFT OUTER JOIN Factory ON Currentstatus.Factoryid = Factory.Factoryid " & vbCrLf & _
                    "INNER JOIN Workflowstep ON Workflowstep.Workflowstepid = Currentstatus.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Workflow ON Workflow.Workflowid = WorkflowStep.Workflowid " & vbCrLf & _
                    "INNER JOIN Cdodefinition ON Cdodefinition.Cdodefid = Workflow.Cdotypeid " & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(" & vbCrLf & _
                    "SELECT Workflowstep.Workflowid, Workflowstep.Workflowstepid, Workflowstep.Workflowstepname,Workflowstep.Sequence, (CASE WHEN Workflowstep.Specid = '0000000000000000' THEN Specbase.Revofrcdid ELSE Workflowstep.Specid END) AS Specid" & vbCrLf & _
                    "FROM Workflowstep" & vbCrLf & _
                    "LEFT JOIN Specbase ON Workflowstep.Specbaseid = Specbase.Specbaseid " & vbCrLf & _
                    ") CStep" & vbCrLf & _
                    "ON CStep.Workflowid = Container.Mfgworkflowid" & vbCrLf & _
                    "INNER JOIN Spec ON CStep.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                    "INNER JOIN Historymainline ON Historymainline.Containerid = Container.Containerid" & vbCrLf & _
                    "INNER JOIN Cdodefinition Cdodefinition2 ON Cdodefinition2.Cdodefid = Historymainline.Txntype" & vbCrLf & _
                    "INNER JOIN Employee ON Employee.Employeeid = Historymainline.Employeeid" & vbCrLf & _
                    "WHERE Container.Parentcontainerid IS NULL AND Container.Qty >=0 AND Cdodefinition.Cdoname <> 'MachineMfgWorkFlow'" & vbCrLf & _
                    " AND Container.status=1 " & vbCrLf

            If strContainerName <> "" Then
                strSQL += " AND Container.Containername LIKE '%" & strContainerName & "%'" & vbCrLf
            End If
            If strProductFamilyname <> "" Then
                strSQL += " AND Productfamily.Productfamilyname = '" & strProductFamilyname & "'" & vbCrLf
            End If
            If strProductName <> "" Then
                strSQL += " AND Productbase.Productname LIKE '%" & strProductName & "%'" & vbCrLf
            End If
            If strProductDesc <> "" Then
                strSQL += " AND Product.Description LIKE '%" & strProductDesc & "%'" & vbCrLf
            End If
            If strFactoryName <> "" Then
                strSQL += " AND Factory.FactoryName ='" & strFactoryName & "'" & vbCrLf
            End If
            If boolDispClose = False Then
                strSQL += " AND Container.status=1 " & vbCrLf
            End If
            If strStartDate <> "" Then
                strSQL += "AND Container.originalstartdate >= TO_DATE('" & strStartDate & "','yyyy-MM-dd') " & vbCrLf
            End If
            If strEndDate <> "" Then
                strSQL += "AND Container.originalstartdate <= TO_DATE('" & strEndDate & "','yyyy-MM-dd') " & vbCrLf
            End If
            If strCreateEmployeeName <> "" Then
                strSQL += " AND UPPER(Employee.Employeename) =UPPER('" & strCreateEmployeeName & "') " & vbCrLf
            End If
            strSQL = strSQL & ") Cinfo" & vbCrLf & _
                    "   LEFT OUTER JOIN" & vbCrLf & _
                    "   (" & vbCrLf & _
                    "  SELECT Container.Containername, WorkCenterDispatching.Specid, " & vbCrLf & _
                    "        NVL(SUM(Workingreporthistory.Qty), 0) AS RQty  " & vbCrLf & _
                    "  FROM Container" & vbCrLf & _
                    "  INNER JOIN WorkCenterDispatching ON WorkCenterDispatching.Containerid = Container.Containerid" & vbCrLf & _
                    "  LEFT JOIN Workingreporthistory ON WorkCenterDispatching.WorkCenterDispatchingid = Workingreporthistory.WorkCenterDispatchingid " & vbCrLf & _
                    "  LEFT JOIN Historymainline ON Historymainline.Historymainlineid = Workingreporthistory.Historymainlineid " & vbCrLf & _
                    "  WHERE NVL(Historymainline.Status, 1) = 1 AND Container.Parentcontainerid IS NULL " & vbCrLf & _
                    "  GROUP BY Container.Containername, WorkCenterDispatching.Specid " & vbCrLf & _
                    "   ) CReport" & vbCrLf & _
                    "   ON Cinfo.Containername = CReport.Containername" & vbCrLf & _
                    "   AND Cinfo.Specid = CReport.Specid" & vbCrLf & _
                    "   LEFT OUTER JOIN" & vbCrLf & _
                    "   (" & vbCrLf & _
                    "   SELECT DISTINCT Container.Containername, SynergicInfo.Specid, (case when Speclastcheckhistory.Historymainlineid IS NULL then '2' else '1' end) as ckZGStatus" & vbCrLf & _
                    "   FROM Container" & vbCrLf & _
                    "   INNER JOIN SynergicInfo ON SynergicInfo.Containerid = Container.Containerid" & vbCrLf & _
                    "   INNER JOIN SynergicInfoSynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicinfoid = Synergicinfo.Synergicinfoid" & vbCrLf & _
                    "   INNER JOIN SynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicspecinfoid = SynergicSpecInfo.Synergicspecinfoid" & vbCrLf & _
                    "   LEFT JOIN Historymainline ON Historymainline.Containerid=SynergicInfo.Containerid AND SynergicInfo.Specid=Historymainline.Specid" & vbCrLf & _
                    "   LEFT JOIN Speclastcheckhistory on Speclastcheckhistory.Historymainlineid=Historymainline.Historymainlineid" & vbCrLf & _
                    "   WHERE Container.Parentcontainerid IS NULL" & vbCrLf & _
                    "   ) CChange" & vbCrLf & _
                    "   ON Cinfo.Containername = CChange.Containername" & vbCrLf & _
                    "   AND Cinfo.Specid = CChange.Specid " & vbCrLf & _
                    "   LEFT OUTER JOIN" & vbCrLf & _
                    "   (" & vbCrLf & _
                    "   SELECT DISTINCT Container.Containername, Qualityrecord.Specid" & vbCrLf & _
                    "   FROM Container" & vbCrLf & _
                    "   INNER JOIN Qualityrecord ON Qualityrecord.Containerid = Container.Containerid" & vbCrLf & _
                    "   WHERE Container.Parentcontainerid IS NULL AND Qualityrecord.Recordstatus <> 4" & vbCrLf & _
                    "   ) CQualityrecord" & vbCrLf & _
                    "   ON Cinfo.Containername = CQualityrecord.Containername" & vbCrLf & _
                    "   AND Cinfo.Specid = CQualityrecord.Specid " & vbCrLf & _
                    "" & vbCrLf & _
                    "   LEFT OUTER JOIN" & vbCrLf & _
                    "    (" & vbCrLf & _
                    "SELECT DISTINCT CLastMoveTemp.Containername, CLastMoveTemp.Specid,  CLastMoveTemp.Checktype" & vbCrLf & _
                    "FROM" & vbCrLf & _
                    "(SELECT Container.Containername, Spec.Specid,  Speclastcheckhistory.Checktype" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "INNER JOIN Historymainline ON Container.Containerid = Historymainline.Containerid" & vbCrLf & _
                    "INNER JOIN Speclastcheckhistory ON Speclastcheckhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN Workflowstep ON Workflowstep.Workflowstepid = Historymainline.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Spec ON Spec.Specid = Workflowstep.Specid" & vbCrLf & _
                    "WHERE Historymainline.Status = 1 " & vbCrLf & _
                    "AND Container.Parentcontainerid IS NULL AND Workflowstep.Specbaseid = '0000000000000000'" & vbCrLf & _
                    "UNION ALL" & vbCrLf & _
                    "SELECT Container.Containername, Spec.Specid,  Speclastcheckhistory.Checktype" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "INNER JOIN Historymainline ON Container.Containerid = Historymainline.Containerid" & vbCrLf & _
                    "INNER JOIN Speclastcheckhistory ON Speclastcheckhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN Workflowstep ON Workflowstep.Workflowstepid = Historymainline.Workflowstepid" & vbCrLf & _
                    "INNER JOIN Specbase ON Specbase.Specbaseid = Workflowstep.Specbaseid" & vbCrLf & _
                    "INNER JOIN Spec ON Spec.Specid = Specbase.Revofrcdid" & vbCrLf & _
                    "WHERE Historymainline.Status = 1 " & vbCrLf & _
                    "AND Container.Parentcontainerid IS NULL AND Workflowstep.Specid = '0000000000000000'" & vbCrLf & _
                    ") CLastMoveTemp" & vbCrLf & _
                    ") CLastMove" & vbCrLf & _
                    "    ON Cinfo.Containername = CLastMove.Containername" & vbCrLf & _
                    "   AND Cinfo.Specid = CLastMove.Specid " & vbCrLf

            strSQL = strSQL & "    ) CFin" & vbCrLf & _
                    "   ORDER BY CFin.IsMain, CFin.Containername, CFin.Sequence " & vbCrLf

            Dim dtSpec As DataTable = dal.GetCheckTempInfo(strSQL)
            dtSpec = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtSpec)

            strSQL = "SELECT distinct CInfo.Containername, CInfo.Specname, CInfo.Fullname, CInfo.Submitdate, CInfo.Synergicinfoname, CInfo.Returnnotes, " & vbCrLf & _
                    "       CInfo.SynergicStatus, CInfo.Specindex, CInfo.Specid" & vbCrLf & _
                    "FROM" & vbCrLf & _
                    "(SELECT Container.Containername, Specbase.Specname, Employee.Fullname, Synergicinfo.Submitdate, Synergicinfo.Synergicinfoname, Synergicinfo.Returnnotes, " & vbCrLf & _
                    "       (CASE WHEN Synergicinfo.Returnspecid IS NULL THEN '外协加工' ELSE '外协完成' END) AS SynergicStatus, SynergicSpecInfo.Specindex, Spec.Specid" & vbCrLf & _
                    "FROM Synergicinfo " & vbCrLf & _
                    "INNER JOIN " & vbCrLf & _
                    "(SELECT MAX(Submitdate) AS Submitdate " & vbCrLf & _
                    "FROM Synergicinfo GROUP BY Synergicinfo.Containerid, Synergicinfo.Specid) CMax " & vbCrLf & _
                    "ON CMax.Submitdate = Synergicinfo.Submitdate" & vbCrLf & _
                    "INNER JOIN Container ON Container.Containerid = Synergicinfo.Containerid" & vbCrLf & _
                    "INNER JOIN SynergicInfoSynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicinfoid = Synergicinfo.Synergicinfoid" & vbCrLf & _
                    "INNER JOIN SynergicSpecInfo ON SynergicSpecInfo.Synergicspecinfoid = SynergicInfoSynergicSpecInfo.Synergicspecinfoid" & vbCrLf & _
                    "INNER JOIN Spec ON SynergicSpecInfo.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                    "LEFT OUTER JOIN Employee ON Synergicinfo.Submitemployeeid = Employee.Employeeid" & vbCrLf & _
                    "WHERE Synergicinfo.Returnspecid IS NOT NULL AND SynergicSpecInfo.Synergictype = 1 AND NVL(SynergicSpecInfo.Isreturnspec, 0) <> 1" & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            strSQL = strSQL & "UNION ALL" & vbCrLf & _
                    "SELECT Container.Containername, Specbase.Specname, Employee.Fullname, Synergicinfo.Submitdate, Synergicinfo.Synergicinfoname, Synergicinfo.Returnnotes, " & vbCrLf & _
                    "       (CASE WHEN Synergicinfo.Returnspecid IS NULL THEN '外协加工' ELSE '外协完成' END) AS SynergicStatus, SynergicSpecInfo.Specindex, Spec.Specid" & vbCrLf & _
                    "FROM Synergicinfo " & vbCrLf & _
                    "INNER JOIN " & vbCrLf & _
                    "(SELECT MAX(Submitdate) AS Submitdate " & vbCrLf & _
                    "FROM Synergicinfo GROUP BY Synergicinfo.Containerid, Synergicinfo.Specid) CMax " & vbCrLf & _
                    "ON CMax.Submitdate = Synergicinfo.Submitdate" & vbCrLf & _
                    "INNER JOIN Container ON Container.Containerid = Synergicinfo.Containerid" & vbCrLf & _
                    "INNER JOIN SynergicInfoSynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicinfoid = Synergicinfo.Synergicinfoid" & vbCrLf & _
                    "INNER JOIN SynergicSpecInfo ON SynergicSpecInfo.Synergicspecinfoid = SynergicInfoSynergicSpecInfo.Synergicspecinfoid" & vbCrLf & _
                    "INNER JOIN Spec ON SynergicSpecInfo.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                    "LEFT OUTER JOIN Employee ON Synergicinfo.Submitemployeeid = Employee.Employeeid " & vbCrLf & _
                    "WHERE Synergicinfo.Returnspecid IS NOT NULL AND SynergicSpecInfo.Synergictype = 2" & vbCrLf
            If strContainerName <> "" Then
                strSQL = strSQL & "AND Container.ContainerName = '" & strContainerName & "'" & vbCrLf
            End If
            strSQL = strSQL & ") CInfo" & vbCrLf & _
                    "ORDER BY CInfo.Synergicinfoname, CInfo.Specindex " & vbCrLf

            Dim dtSynergic As DataTable = dal.GetCheckTempInfo(strSQL)
            dtSynergic = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtSynergic)

            Dim ds As New DataSet
            ds.Tables.Add(dtMain)
            ds.Tables(0).TableName = "dtMain"
            ds.Tables.Add(dtSpec)
            ds.Tables(1).TableName = "dtSpec"
            ds.Tables.Add(dtSynergic)
            ds.Tables(2).TableName = "dtSynergic"
            oResult.DBDataSet = ds
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try


    End Function


    ''' <summary>
    ''' 获取产品系列
    ''' </summary>
    ''' <returns></returns>
    Private Function GetProductFamily(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetProductFamily
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL As String
            strSQL = "SELECT ProductFamily.ProductFamilyname FROM ProductFamily ORDER BY ProductFamily.ProductFamilyname" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取产品类型
    ''' </summary>
    ''' <returns></returns>
    Private Function GetProductType(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetProductType
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL As String
            strSQL = "SELECT ProductType.ProductTypename FROM ProductType ORDER BY ProductType.ProductTypename"

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取所有车间
    ''' </summary>
    ''' <returns></returns>
    Private Function GetFactory(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetFactory
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL As String
            strSQL = "SELECT Factory.Factoryname FROM Factory ORDER BY Factory.Factoryname"

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取批次信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetContainerInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid, strSpecid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If
            strSpecid = ""
            If QueryCondition.Keys.Contains("Specid") = True Then
                strSpecid = QueryCondition("Specid").ToString()
            End If

            strSQL = "SELECT Mfgorder.mfgordername, Inventtaskinfo.Inventtaskinfoname,  TO_CHAR(Mfgorder.Plannedcompletiondate , 'RRRR-MM-DD') AS Plannedcompletiondate ,Mfgorder.Qty as ddqty," & vbCrLf & _
                    "       Container.Containername, DECODE(Inventtaskinfo.Urgent,'0','普通','1','紧急','2','特急') AS Urgent, Container.Qty as pcqty,Productbase.Productname," & vbCrLf & _
                    "       Product.Description,Productfamily.Productfamilyname,Producttype.Producttypename, CReport.Qty AS ReportQty, NVL(CEligible.eligibilityqty, 0) AS Eligibilityqty, " & vbCrLf & _
                    "       CQualityrecord.QualityrecordQty , CScrapinfo.Scrapqty, Ordertype.Ordertypename, Product.Productrevision,Container.OriginalQty," & vbCrLf & _
                    "       (SELECT Specbase.Specname FROM Spec INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid " & vbCrLf & _
                    "       WHERE Spec.Specid = '" & strSpecid & "'" & vbCrLf & _
                    "       ) AS Specname," & vbCrLf & _
                    "       (SELECT Spec.Specrevision FROM Spec" & vbCrLf & _
                    "       WHERE Spec.Specid = '" & strSpecid & "'" & vbCrLf & _
                    "       ) AS Specrev, Container.Childcount,movehistory.notes,movehistory.tospecid" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "LEFT OUTER JOIN Mfgorder ON Container.mfgorderid=Mfgorder.mfgorderid " & vbCrLf & _
                    "LEFT OUTER JOIN Inventtaskinfo ON Mfgorder.Inventtaskinfoid = Inventtaskinfo.Inventtaskinfoid" & vbCrLf & _
                    "LEFT OUTER JOIN Ordertype ON Mfgorder.Ordertypeid = Ordertype.Ordertypeid" & vbCrLf & _
                    "INNER JOIN Product on Product.Productid = Container.Productid " & vbCrLf & _
                    "INNER JOIN Productbase on Productbase.Productbaseid =Product.Productbaseid " & vbCrLf & _
                    "LEFT OUTER JOIN Productfamily on Productfamily.Productfamilyid=Product.Productfamilyid " & vbCrLf & _
                    "LEFT OUTER JOIN Producttype on Producttype.producttypeid=Product.producttypeid " & vbCrLf & _
                    "LEFT OUTER JOIN  " & vbCrLf & _
                    "(SELECT WorkCenterDispatching.Containerid, WorkCenterDispatching.Specid, " & vbCrLf & _
                    "        NVL(SUM(Workingreporthistory.Qty), 0) AS Qty  " & vbCrLf & _
                    "FROM WorkCenterDispatching  " & vbCrLf & _
                    "INNER JOIN Workingreporthistory ON WorkCenterDispatching.WorkCenterDispatchingid = Workingreporthistory.WorkCenterDispatchingid " & vbCrLf & _
                    "INNER JOIN Historymainline ON Historymainline.Historymainlineid = Workingreporthistory.Historymainlineid " & vbCrLf & _
                    "WHERE Historymainline.Reversalstatus = 1 " & vbCrLf & _
                    "AND WorkCenterDispatching.Specid ='" & strSpecid & "' AND WorkCenterDispatching.Containerid='" + strContainerid + "'" & vbCrLf & _
                    "GROUP BY WorkCenterDispatching.Containerid, WorkCenterDispatching.Specid " & vbCrLf & _
                    ") CReport  " & vbCrLf & _
                    "  ON CReport.Containerid = Container.Containerid " & vbCrLf & _
                    "LEFT OUTER JOIN  " & vbCrLf & _
                    "(SELECT Historymainline.Containerid, CSpec.Specid,  Speclastcheckhistory.Qualifiedqty as eligibilityqty" & vbCrLf & _
                    "FROM Speclastcheckhistory" & vbCrLf & _
                    "INNER JOIN Historymainline ON Historymainline.Historymainlineid = Speclastcheckhistory.Historymainlineid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(SELECT Workflowstep.Workflowstepid, " & vbCrLf & _
                    "        (CASE WHEN Workflowstep.Specbaseid = '0000000000000000' THEN Workflowstep.Specid ELSE Workflowstep.Specbaseid END) AS Specid" & vbCrLf & _
                    "FROM Workflowstep" & vbCrLf & _
                    "LEFT JOIN Spec ON Workflowstep.Specid = Spec.Specid" & vbCrLf & _
                    "LEFT JOIN Specbase ON Workflowstep.Specbaseid = Specbase.Specbaseid) CSpec" & vbCrLf & _
                    "ON CSpec.Workflowstepid = Historymainline.Workflowstepid" & vbCrLf & _
                    "WHERE Historymainline.Reversalstatus = 1 " & vbCrLf

            strSQL = strSQL & ") CEligible  " & vbCrLf & _
                    "  ON CEligible.Containerid = Container.Containerid " & vbCrLf & _
                    "LEFT OUTER JOIN  " & vbCrLf & _
                    "(SELECT Containerid, Specid, SUM(Scrapqty) AS Scrapqty " & vbCrLf & _
                    "FROM Scrapinfo " & vbCrLf & _
                    "WHERE Scrapinfo.Specid ='" & strSpecid & "' AND Scrapinfo.Containerid='" + strContainerid + "'" & vbCrLf & _
                    "GROUP BY Containerid, Specid " & vbCrLf & _
                    ") CScrapinfo " & vbCrLf & _
                    " ON CScrapinfo.Containerid = Container.Containerid " & vbCrLf & _
                    "LEFT OUTER JOIN " & vbCrLf & _
                    "(SELECT Containerid, Specid, SUM(Qty) AS QualityrecordQty " & vbCrLf & _
                    "FROM Qualityrecord " & vbCrLf & _
                    "WHERE Qualityrecord.Specid ='" & strSpecid & "' AND Qualityrecord.Containerid='" + strContainerid + "'" & vbCrLf & _
                    "GROUP BY Qualityrecord.Containerid, Qualityrecord.Specid " & vbCrLf & _
                    ") CQualityrecord " & vbCrLf & _
                    "ON CQualityrecord.Containerid = Container.Containerid " & vbCrLf & _
                    "  left outer join " & vbCrLf & _
                    " ( " & vbCrLf & _
                    "     select c.containerid,hm.specid,sum(con.eligibilityqty) as eligibilityqty" & vbCrLf & _
                   "      from conventioncheckhistory con" & vbCrLf & _
                   "      inner join historymainline hm on hm.historymainlineid=con.historymainlineid" & vbCrLf & _
                   "      inner join container c on c.containerid=hm.containerid" & vbCrLf & _
                   "      where hm.containerid='" + strContainerid + "' and hm.specid='" + strSpecid + "' and hm.Reversalstatus=1" & vbCrLf & _
                   "      group by c.containerid,hm.specid" & vbCrLf & _
                   "   ) Gongxu on Gongxu.containerid=Container.Containerid " & vbCrLf & _
                   "   left join historymainline on historymainline.containerid=container.containerid" & vbCrLf & _
                   "   left join movehistory on movehistory.historymainlineid=historymainline.historymainlineid and movehistory.tospecid='" + strSpecid + "'" & vbCrLf & _
                    "WHERE Container.Containerid='" + strContainerid + "' " & vbCrLf
            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("Specname", dtMain)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function
    ''' <summary>
    ''' 获取批次信息新（弹出页面）
    ''' create:Wangjh 2017-8-22
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetContainerInfoForPopupNew(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable Implements uMESContainerReportIBusiness.GetContainerInfoForPopupNew
        Dim strSql = " SELECT c.containername,pb.productname,p.description,c.qty,substr(ws.workflowstepname,0,instr(ws.workflowstepname,'-')-1) specno,ws.workflowstepname, csrr.reportqty," &
                     "   csrr.passqty,csrr.lossqty,pf.productfamilyname,pt.producttypename,m.qty planqty,c.qty wipqty" &
                    " FROM  container c" &
                    "  LEFT JOIN product p ON p.productid=c.productid" &
                    "  LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid" &
                    "  LEFT JOIN productfamily pf ON pf.productfamilyid=p.productfamilyid " &
                    " LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid" &
                    " LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid"
        strSql += " LEFT JOIN (       " &
                 " SELECT SUM( csr.reportqty) reportqty,SUM(csr.passqty) passqty,SUM(csr.lossqty) lossqty,csr.containerid,csr.workflowstepid FROM containersteprecord csr"
        strSql += String.Format(" WHERE csr.containerid='{0}'", QueryCondition("ContainerID"))
        If QueryCondition.ContainsKey("WorkFlowStepID") Then
            If Not String.IsNullOrWhiteSpace(QueryCondition("WorkFlowStepID")) Then
                strSql += String.Format(" and csr.workflowstepid='{0}'", QueryCondition("WorkFlowStepID"))
            Else
                strSql += String.Format(" and csr.specid='{0}'", QueryCondition("SpecID"))
            End If
        End If
        strSql += " GROUP BY csr.containerid,csr.workflowstepid " &
                 " )  csrr ON csrr.containerid=c.containerid"
        strSql += " left join workflowstep ws on ws.workflowstepid=csrr.workflowstepid"
        strSql += String.Format(" WHERE c.containerid='{0}'", QueryCondition("ContainerID"))
        'If QueryCondition.ContainsKey("WorkFlowStepID") Then
        '    If Not String.IsNullOrWhiteSpace(QueryCondition("WorkFlowStepID")) Then
        '        strSql += String.Format(" and WS.workflowstepid='{0}'", QueryCondition("WorkFlowStepID"))
        '    End If
        'End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        '屏蔽注释：采用自己写的方法来处理工序
        'dt = DataAdjust.FormatDataSetSpecNameByColumName("workflowstepname", dt)
        GetContainerInfoForPopupNew = dt
    End Function

    ''' <summary>
    ''' 获取派工信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetGetWorkDispatchInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetGetWorkDispatchInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid, strSpecid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If
            strSpecid = ""
            If QueryCondition.Keys.Contains("Specid") = True Then
                strSpecid = QueryCondition("Specid").ToString()
            End If

            strSQL = "SELECT WorkCenterDispatching.Workcenterdispatchingname, WorkCenterDispatching.Qty,Team.Teamname, WorkCenterDispatching.PlannedCompletionDate," & vbCrLf & _
                    "       Employee.Fullname, WorkCenterDispatching.Dispatchdate, WorkCenterDispatching.Workcenterdispatchingid, Mainemployee.FullName AS MainName, Minoremployee.FullName AS MinorName," & vbCrLf & _
                    "       DECODE(WorkCenterDispatching.Status, 0, '已派工','1','已接收','2','已报工','3','已完成','4','已关闭') AS Ckpgdstatusname, WorkCenterDispatching.ReceiveDate " & vbCrLf & _
                    "FROM WorkCenterDispatching " & vbCrLf & _
                    "INNER JOIN Container ON Container.Containerid = WorkCenterDispatching.Containerid" & vbCrLf & _
                    "INNER JOIN Spec ON Spec.Specid = WorkCenterDispatching.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                    "LEFT OUTER JOIN Team ON WorkCenterDispatching.Teamid = Team.Teamid" & vbCrLf & _
                    "INNER JOIN Employee ON Employee.Employeeid = WorkCenterDispatching.Dispatchemployeeid " & vbCrLf & _
                    "LEFT OUTER JOIN Employee Mainemployee ON Mainemployee.Employeeid = WorkCenterDispatching.Mainmfgemployeeid" & vbCrLf & _
                    "LEFT OUTER JOIN Employee Minoremployee ON Minoremployee.Employeeid = WorkCenterDispatching.Minormfgemployeeid" & vbCrLf & _
                    "WHERE WorkCenterDispatching.Status <> '4' " & vbCrLf & _
                    " AND Container.ContainerID = '" & strContainerid & "' AND Spec.SpecID ='" & strSpecid & "'" & vbCrLf & _
                    "ORDER BY WorkCenterDispatching.Dispatchdate" & vbCrLf

            '" AND Container.ContainerID = '" & strContainerid & "' AND Spec.SpecID ='" & strSpecid & "'" & vbCrLf & _

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            strSQL = "SELECT WorkCenterDispatching.Workcenterdispatchingid, WorkCenterDispatchingChildCont.Childcontainer" & vbCrLf & _
                    "FROM WorkCenterDispatching " & vbCrLf & _
                    "INNER JOIN Container ON Container.Containerid = WorkCenterDispatching.Containerid" & vbCrLf & _
                    "INNER JOIN Spec ON Spec.Specid = WorkCenterDispatching.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                    "INNER JOIN WorkCenterDispatchingChildCont ON WorkCenterDispatchingChildCont.Workcenterdispatchingid = WorkCenterDispatching.Workcenterdispatchingid" & vbCrLf & _
                    "WHERE WorkCenterDispatching.Status <> '4' " & vbCrLf & _
                    "AND Container.ContainerID = '" & strContainerid & "' AND Spec.SpecID ='" & strSpecid & "'" & vbCrLf
            Dim dtDetail As DataTable = dal.GetCheckPointResult(strSQL)


            Dim ds As New DataSet
            ds.Tables.Add(dtMain.Copy())
            ds.Tables(0).TableName = "dtMain"
            ds.Tables.Add(dtDetail.Copy())
            ds.Tables(1).TableName = "dtDetail"

            Dim parentColunms = New DataColumn(0) {ds.Tables(0).Columns("Workcenterdispatchingid")}
            Dim childColunms = New DataColumn(0) {ds.Tables(1).Columns("Workcenterdispatchingid")}
            ds.Relations.Add("rs0", parentColunms, childColunms)

            oResult.DBDataSet = ds


            'oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取报工信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetWorkReportInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetWorkReportInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid, strSpecid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If
            strSpecid = ""
            If QueryCondition.Keys.Contains("Specid") = True Then
                strSpecid = QueryCondition("Specid").ToString()
            End If

            strSQL = "SELECT Workingreporthistory.Qty, NVL(CEligible.Eligibilityqty, 0) AS Eligibilityqty, Employee.Fullname,Historymainline.Txndate, " & vbCrLf & _
                "     DECODE(Workingreporthistory.Reporttype2,'0','首件','1','中件','2','末件','3','其他') AS Reporttype2," & vbCrLf & _
                "       Workingreporthistory.Workingreporthistoryid, Container.ContainerName, Resourcedef.Resourcename, hml.txndate as checkdate," & vbCrLf & _
                " checkEmployee.Fullname AS CheckEmployee " & vbCrLf & _
                "FROM WorkCenterDispatching " & vbCrLf & _
                "INNER JOIN Container ON Container.Containerid = WorkCenterDispatching.Containerid" & vbCrLf & _
                "INNER JOIN Workingreporthistory ON WorkCenterDispatching.Workcenterdispatchingid = Workingreporthistory.Workcenterdispatchingid" & vbCrLf & _
                "INNER JOIN Spec ON Spec.Specid = WorkCenterDispatching.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                "LEFT OUTER JOIN Resourcedef ON Resourcedef.Resourceid = Workingreporthistory.Resourceid" & vbCrLf & _
                "INNER JOIN Historymainline ON Workingreporthistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                "INNER JOIN Employee ON  Workingreporthistory.reportemployeeid = Employee.Employeeid " & vbCrLf & _
                "LEFT OUTER JOIN " & _
                "(" & vbCrLf & _
                "SELECT Conventioncheckhistory.Workingreportid, SUM(Conventioncheckhistory.Eligibilityqty) AS Eligibilityqty,Conventioncheckhistory.Historymainlineid" & vbCrLf & _
                "FROM Conventioncheckhistory" & vbCrLf & _
                "GROUP BY Conventioncheckhistory.Workingreportid,Conventioncheckhistory.Historymainlineid " & vbCrLf & _
                ") CEligible " & vbCrLf & _
                " ON Workingreporthistory.Workingreporthistoryid = CEligible.Workingreportid" & vbCrLf & _
                "left join Historymainline hml on hml.Historymainlineid=CEligible.Historymainlineid " & vbCrLf & _
                "LEFT JOIN Employee checkEmployee ON Workingreporthistory.Mutualcheckemployeeid = checkEmployee.Employeeid " & vbCrLf & _
                "left join  " & vbCrLf & _
                "( " & vbCrLf & _
                "SELECT  Workflowstep.Sequence,Workflowstep.Workflowstepid,Workflowstep.Workflowstepname, Workflowstep.Workflowid, " & vbCrLf & _
                "(CASE WHEN Workflowstep.Specid = '0000000000000000' " & vbCrLf & _
                "THEN Specbase.Revofrcdid ELSE Workflowstep.Specid END) AS Specid " & vbCrLf & _
                " FROM Workflowstep " & vbCrLf & _
                " LEFT JOIN Specbase ON Specbase.Specbaseid = Workflowstep.Specbaseid " & vbCrLf & _
                ") CSpec ON CSpec.Workflowstepid = Historymainline.Workflowstepid " & vbCrLf & _
                "left JOIN Workflow ON Workflow.Workflowid = CSpec.Workflowid " & _
                "left JOIN Cdodefinition ON Workflow.Cdotypeid = Cdodefinition.Cdodefid " & vbCrLf & _
                "left JOIN cdodefinition cdo1 ON Historymainline.txntype = cdo1.cdodefid " & vbCrLf & _
               "WHERE WorkCenterDispatching.Status <> '4' AND Container.ContainerID = '" & strContainerid & "'" & vbCrLf & _
               "AND Spec.SpecID ='" & strSpecid & "' and cdo1.cdoname<>'MoveNonStd' and Historymainline.Reversalstatus=1 " & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)


            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取转工单信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetSynergicInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetSynergicInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid, strSpecid As String
            strSQL = ""
            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If
            strSpecid = ""
            If QueryCondition.Keys.Contains("Specid") = True Then
                strSpecid = QueryCondition("Specid").ToString()
            End If

            'strSQL = "SELECT Synergicinfo.Synergicinfoname, Specbase.Specname, Synergicinfo.Qty, Employee.Fullname, Synergicinfo.Submitdate,  " & vbCrLf & _
            '         "ReturnEmployee.Fullname as ReturnFullname,  " & vbCrLf & _
            '         "re.returndate as Returndate,   " & vbCrLf & _
            '        "'厂内外协' AS Synergictype,Synergicinfo.Synergicinfoname,Synergicinfo.Returnspecid,re.Isreturnspec,NULL workflowstepname,NULL TOworkflowstepname,NULL proposername " & vbCrLf & _
            '        "FROM Synergicinfo " & vbCrLf & _
            '        "INNER JOIN Container ON Container.Containerid = Synergicinfo.Containerid  " & vbCrLf & _
            '        "INNER JOIN Employee ON Employee.Employeeid = Synergicinfo.Submitemployeeid   " & vbCrLf & _
            '        "INNER JOIN SynergicInfoSynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicinfoid = Synergicinfo.Synergicinfoid" & vbCrLf & _
            '        "INNER JOIN SynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicspecinfoid = SynergicSpecInfo.Synergicspecinfoid" & vbCrLf & _
            '        "INNER JOIN Spec ON SynergicSpecInfo.Specid = Spec.Specid " & vbCrLf & _
            '        "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid " & vbCrLf & _
            '        "left join   " & vbCrLf & _
            '        "(  " & vbCrLf & _
            '        "select  Synergicinfo.Containerid,Synergicinfo.Returndate,SynergicSpecInfo.Specid,Synergicinfo.Returnemployeeid,SynergicSpecInfo.Isreturnspec  " & vbCrLf & _
            '        "from  Synergicinfo    " & vbCrLf & _
            '        "inner JOIN SynergicInfoSynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicinfoid = Synergicinfo.Synergicinfoid  " & vbCrLf & _
            '        "inner JOIN SynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicspecinfoid = SynergicSpecInfo.Synergicspecinfoid " & vbCrLf & _
            '        ")re on re.Containerid=Synergicinfo.Containerid and re.Specid=Synergicinfo.Returnspecid  " & vbCrLf & _
            '        "LEFT  JOIN Employee ReturnEmployee ON ReturnEmployee.Employeeid = re.Returnemployeeid   " & vbCrLf & _
            '        "WHERE Container.Status = 1 AND Container.Qty > 0 AND Container.Parentcontainerid IS NULL  " & vbCrLf & _
            '        "AND Synergicinfo.Status =1  AND SynergicSpecInfo.Synergictype = 1 AND NVL(SynergicSpecInfo.Isreturnspec, 0) <> 1" & vbCrLf & _
            '        "AND Container.ContainerID = '" & strContainerid & "'" & vbCrLf & _
            '        "AND Spec.SpecID ='" & strSpecid & "'" & vbCrLf & _
            '        "UNION ALL" & vbCrLf & _
            '        "SELECT Synergicinfo.Synergicinfoname, Specbase.Specname, Synergicinfo.Qty, Employee.Fullname, Synergicinfo.Submitdate,  " & vbCrLf & _
            '        "case when NVL(re.Isreturnspec, 0) ='0' then '' else ReturnEmployee.Fullname end as ReturnFullname,  " & vbCrLf & _
            '         "case when NVL(re.Isreturnspec, 0)='0' then NULL else re.returndate end as Returndate,  " & vbCrLf & _
            '        "'厂内外协' AS Synergictype,Synergicinfo.Synergicinfoname,Synergicinfo.Returnspecid,re.Isreturnspec,NULL workflowstepname,NULL TOworkflowstepname,NULL proposername   " & vbCrLf & _
            '        "FROM Synergicinfo " & vbCrLf & _
            '        "INNER JOIN Container ON Container.Containerid = Synergicinfo.Containerid  " & vbCrLf & _
            '        "INNER JOIN Employee ON Employee.Employeeid = Synergicinfo.Submitemployeeid   " & vbCrLf & _
            '        "INNER JOIN SynergicInfoSynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicinfoid = Synergicinfo.Synergicinfoid" & vbCrLf & _
            '        "INNER JOIN SynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicspecinfoid = SynergicSpecInfo.Synergicspecinfoid" & vbCrLf & _
            '        "INNER JOIN Spec ON SynergicSpecInfo.Specid = Spec.Specid " & vbCrLf & _
            '        "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid " & vbCrLf & _
            '        "left join   " & vbCrLf & _
            '        "(  " & vbCrLf & _
            '        "select  Synergicinfo.Containerid,Synergicinfo.Returndate,SynergicSpecInfo.Specid,Synergicinfo.Returnemployeeid,SynergicSpecInfo.Isreturnspec  " & vbCrLf & _
            '        "from  Synergicinfo    " & vbCrLf & _
            '        "inner JOIN SynergicInfoSynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicinfoid = Synergicinfo.Synergicinfoid  " & vbCrLf & _
            '        "inner JOIN SynergicSpecInfo ON SynergicInfoSynergicSpecInfo.Synergicspecinfoid = SynergicSpecInfo.Synergicspecinfoid " & vbCrLf & _
            '        ")re on re.Containerid=Synergicinfo.Containerid and re.Specid=Synergicinfo.Returnspecid  " & vbCrLf & _
            '        "LEFT  JOIN Employee ReturnEmployee ON ReturnEmployee.Employeeid = re.Returnemployeeid   " & vbCrLf & _
            '        "WHERE Container.Status = 1 AND Container.Qty > 0 AND Container.Parentcontainerid IS NULL  " & vbCrLf & _
            '        "AND Container.ContainerID = '" & strContainerid & "'" & vbCrLf & _
            '        "AND Spec.SpecID ='" & strSpecid & "'" & vbCrLf
            'add:Wangjh 2017-8-22
            ' strSQL += " UNION ALL"
            'modify by tianFW 2018年7月2日 增加,csre.CompleteDate reciveCompleteDate,e4.fullname reciveCompleter 查询 承制分厂完成人和完成日期
            strSQL +=
                " SELECT NULL Synergicinfoname,NULL specname,NULL qty,e1.fullname,csr.senddate Submitdate,e2.fullname ReturnFullname," &
                " csre.ReciveDate Returndate,NULL Synergictype,NULL Synergicinfoname,NULL Returnspecid,NULL Isreturnspec,ws1.workflowstepname," &
                " ws2.workflowstepname TOworkflowstepname,e3.fullname proposername," &
                " csre.CompleteDate reciveCompleteDate,e4.fullname reciveCompleter,e5.fullname ToAcceptor,csr.ToAcceptDate,csre.containerid" &
                " FROM containershiprecord csr" &
                " LEFT JOIN containershiprecive csre ON csre.ContainerShipRecordID=csr.containershiprecordid" &
                " LEFT JOIN employee e1 ON e1.employeeid=csr.sender--转工转出人" & vbCrLf & _
                " LEFT JOIN employee e2 ON e2.employeeid=csre.reciver--转入接收人" & vbCrLf & _
                " LEFT JOIN employee e3 ON e3.employeeid=csr.proposerid--转工申请人" & vbCrLf & _
                " LEFT JOIN employee e4 ON e4.employeeid=csre.employeeid --承制完成人" & vbCrLf & _
                " LEFT JOIN employee e5 ON e5.employeeid=csr.ToAcceptor --承制接收人" & vbCrLf & _
                " LEFT JOIN Workflowstep ws1 ON ws1.workflowstepid=csr.workflowstepid--转工开始工序" & vbCrLf & _
                " LEFT JOIN workflowstep ws2 ON ws2.workflowstepid=csr.toworkflowstepid--转工结束工序" & vbCrLf & _
                " LEFT JOIN WORKFLOWSTEP WS3 ON WS3.WORKFLOWID=WS1.WORKFLOWID AND (WS3.SEQUENCE>=WS1.SEQUENCE AND WS3.SEQUENCE<=WS2.SEQUENCE)"
            strSQL += String.Format(" WHERE csr.isuse=0 AND csre.containerid='{0}'", QueryCondition("ContainerID"))
            If Not String.IsNullOrWhiteSpace(QueryCondition("WorkFlowStepID")) Then
                strSQL += String.Format(" and ws3.workflowstepid='{0}'", QueryCondition("WorkFlowStepID"))
            End If

            strSQL += "UNION"

            strSQL +=
                " SELECT NULL Synergicinfoname,NULL specname,NULL qty,e1.fullname,csr.senddate Submitdate,e2.fullname ReturnFullname," &
                " csre.ReciveDate Returndate,NULL Synergictype,NULL Synergicinfoname,NULL Returnspecid,NULL Isreturnspec,ws1.workflowstepname," &
                " ws2.workflowstepname TOworkflowstepname,e3.fullname proposername," &
                " csre.CompleteDate reciveCompleteDate,e4.fullname reciveCompleter,e5.fullname ToAcceptor,csr.ToAcceptDate,csr.containerid" &
                " FROM containershiprecord csr" &
                " LEFT JOIN containershiprecive csre ON csre.ContainerShipRecordID=csr.containershiprecordid and csr.containerid=csre.containerid" &
                " LEFT JOIN employee e1 ON e1.employeeid=csr.sender--转工转出人" & vbCrLf & _
                " LEFT JOIN employee e2 ON e2.employeeid=csre.reciver--转入接收人" & vbCrLf & _
                " LEFT JOIN employee e3 ON e3.employeeid=csr.proposerid--转工申请人" & vbCrLf & _
                " LEFT JOIN employee e4 ON e4.employeeid=csre.employeeid --承制完成人" & vbCrLf & _
                " LEFT JOIN employee e5 ON e5.employeeid=csr.ToAcceptor --承制接收人" & vbCrLf & _
                " LEFT JOIN Workflowstep ws1 ON ws1.workflowstepid=csr.workflowstepid--转工开始工序" & vbCrLf & _
                " LEFT JOIN workflowstep ws2 ON ws2.workflowstepid=csr.toworkflowstepid--转工结束工序" & vbCrLf & _
                " LEFT JOIN WORKFLOWSTEP WS3 ON WS3.WORKFLOWID=WS1.WORKFLOWID AND (WS3.SEQUENCE>=WS1.SEQUENCE AND WS3.SEQUENCE<=WS2.SEQUENCE)"
            strSQL += String.Format(" WHERE csr.isuse=0 AND csr.containerid='{0}'", QueryCondition("ContainerID"))
            If Not String.IsNullOrWhiteSpace(QueryCondition("WorkFlowStepID")) Then
                strSQL += String.Format(" and ws3.workflowstepid='{0}'", QueryCondition("WorkFlowStepID"))
            End If
            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("Specname", dtMain)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取检测项信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetCheckPointInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetCheckPointInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid, strSpecid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If
            strSpecid = ""
            If QueryCondition.Keys.Contains("Specid") = True Then
                strSpecid = QueryCondition("Specid").ToString()
            End If

            strSQL = "SELECT Specbase.Specname, DECODE(Checkpointgroup.Grouptype, 1,'标准', 2, '临时') AS Grouptype, Checkpointgroup.Notes, " & vbCrLf & _
                    "       Checkpoints.Checkpointnote, Checkpoints.Inputnum, '' AS CheckPointDisp, Employee.Fullname, Checkpointgroup.Submitdate" & vbCrLf & _
                    "FROM Checkpointgroup" & vbCrLf & _
                    "INNER JOIN " & vbCrLf & _
                    "(" & vbCrLf & _
                    "SELECT Checkpointgroup.Productid, Checkpointgroup.Specid, Checkpointgroup.Grouptype, MAX(Checkpointgroup.Submitdate) AS Submitdate" & vbCrLf & _
                    "FROM Checkpointgroup" & vbCrLf & _
                    "GROUP BY  Checkpointgroup.Productid, Checkpointgroup.Specid, Checkpointgroup.Grouptype" & vbCrLf & _
                    ") CMax" & vbCrLf & _
                    "ON Checkpointgroup.Productid =  CMax.Productid" & vbCrLf & _
                    "AND Checkpointgroup.Specid =  CMax.Specid" & vbCrLf & _
                    "AND Checkpointgroup.Submitdate = CMax.Submitdate" & vbCrLf & _
                    "INNER JOIN Product ON Checkpointgroup.Productid = Product.Productid" & vbCrLf & _
                    "INNER JOIN Productbase ON Productbase.Productbaseid = Product.Productbaseid" & vbCrLf & _
                    "INNER JOIN Spec ON Checkpointgroup.Specid = Spec.Specid" & vbCrLf & _
                    "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                    "INNER JOIN Container ON Container.Productid = Product.Productid" & vbCrLf & _
                    "LEFT JOIN Checkpointgroupcheckpoints ON Checkpointgroupcheckpoints.Checkpointgroupid = Checkpointgroup.Checkpointgroupid" & vbCrLf & _
                    "INNER JOIN Checkpoints ON Checkpointgroupcheckpoints.Checkpointsid = Checkpoints.Checkpointsid" & vbCrLf & _
                    "INNER JOIN Employee ON Checkpointgroup.Submitemployeeid = Employee.Employeeid" & vbCrLf & _
                    "WHERE Container.ContainerID = '" & strContainerid & "'" & vbCrLf
            If strSpecid <> "" Then
                strSQL = strSQL & "AND Spec.SpecID ='" & strSpecid & "'" & vbCrLf
            End If

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("Specname", dtMain)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function
    ''' <summary>
    ''' 获取检测项信息新(弹出界面)
    ''' create:Wangjh 2017-8-22
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetStepPointInfo(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable Implements uMESContainerReportIBusiness.GetStepPointInfo
        Dim strSql As String = " SELECT scp.name,scp.checkpointnote,DECODE(SCp.CHECKINPUTTYPE, '1', '标准', '2', '临时', CHECKINPUTTYPE) CHECKINPUTTYPENAME," &
                               " DECODE(SCp.CHECKTYPE, '1', '专用', '2', '共用', CHECKTYPE) CHECKTYPENAME,DECODE(SCp.KEYCHECK, '1', '是', '0', '否', KEYCHECK) KEYCHECKNAME," &
                               " DECODE(SCp.MEOPCHECK, '1', '是', '0', '否', MEOPCHECK) MEOPCHECKNAME,DECODE(SCp.QUALITYCHECK, '1', '是', '0', '否', QUALITYCHECK) QUALITYCHECKNAME," &
                               " DECODE(SCp.TCCHECKPOINTNOTE, NULL, '否', '是') ISTCM,e2.fullname WorkerName,scpr.createdate,scpr.workcheckpointresultnote," &
                               " e1.fullname CheckerName,scpr.checkdate,scpr.checkpointresultnote,c.containername" &
                               " FROM stepcheckpointresult scpr" &
                                " inner JOIN stepcheckpoint scp ON scp.stepcheckpointid=scpr.stepcheckpointid" &
                                "  LEFT JOIN employee e1 ON e1.employeeid=scpr.checkerid" &
                                " LEFT JOIN employee e2 ON e2.employeeid=scpr.workerid " &
                                " LEFT JOIN container c ON c.containerid = scpr.childcontainerid"
        strSql += String.Format(" WHERE scpr.containerid='{0}' and scp.workflowstepid='{1}'", QueryCondition("ContainerID"), QueryCondition("WorkFlowStepID"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        GetStepPointInfo = dt
    End Function

    ''' <summary>
    ''' 获取结活信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetSpecLastCheckInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetSpecLastCheckInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid, strSpecid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If
            strSpecid = ""
            If QueryCondition.Keys.Contains("Specid") = True Then
                strSpecid = QueryCondition("Specid").ToString()
            End If

            strSQL = "SELECT NVL(Speclastcheckhistory.Qualifiedqty, 0) AS Qualifiedqty, NVL(Speclastcheckhistory.Defectqty, 0) AS Defectqty, NVL(Speclastcheckhistory.Scrapqty, 0) AS Scrapqty, NVL(Speclastcheckhistory.Floorqty, 0) AS Floorqty," & vbCrLf & _
                    "       Employee.FullName AS EmployeeName,TO_CHAR(Historymainline.Txndate, 'RRRR-MM-DD HH24:MI:SS') AS Ckchecktime" & vbCrLf & _
                    "FROM Speclastcheckhistory" & vbCrLf & _
                    "INNER JOIN Historymainline ON Speclastcheckhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN Container ON Historymainline.Containerid = Container.Containerid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(SELECT Workflowstep.Workflowstepid, Spec.Specid" & vbCrLf & _
                    "FROM Workflowstep" & vbCrLf & _
                    "INNER JOIN Spec ON Workflowstep.Specid = Spec.Specid" & vbCrLf & _
                    "WHERE Workflowstep.Specbaseid = '0000000000000000'" & vbCrLf & _
                    "UNION ALL" & vbCrLf & _
                    "SELECT Workflowstep.Workflowstepid, Specbase.Revofrcdid AS Specid" & vbCrLf & _
                    "FROM Workflowstep" & vbCrLf & _
                    "INNER JOIN Specbase ON Workflowstep.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
                    "WHERE Workflowstep.Specid = '0000000000000000'" & vbCrLf & _
                    ") CSpec" & vbCrLf & _
                    "ON CSpec.Workflowstepid = Historymainline.Workflowstepid" & vbCrLf & _
                    "LEFT OUTER JOIN Employee ON Employee.Employeeid = Speclastcheckhistory.reportemployeeid" & vbCrLf & _
                    "WHERE Container.Parentcontainerid IS NULL AND Container.Containername <> Container.containerid AND Historymainline.Reversalstatus = 1" & vbCrLf & _
                    "AND Container.ContainerID = '" & strContainerid & "'" & vbCrLf & _
                    "AND CSpec.SpecID ='" & strSpecid & "'" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取质量记载信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetQualityRecordInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetQualityRecordInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid, strSpecid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If
            strSpecid = ""
            If QueryCondition.Keys.Contains("Specid") = True Then
                strSpecid = QueryCondition("Specid").ToString()
            End If

            strSQL = "SELECT Qualityrecord.Qualityrecordid, Qualityrecord.Qualityrecordname, Container.Containername, Specbase.Specname, Qualityrecord.Qty AS QualityQty, " & vbCrLf & _
            "       Spec.Specrevision, Employee.Fullname, TO_CHAR(Qualityrecord.Checkdate,'RRRR-MM-DD HH24:MI:SS') AS Checkdate, Container.Qty, " & vbCrLf & _
            "       DECODE(Qualityrecord.Recordstatus, '1','检验提交','2','工艺处理','3','检验确认','4','已删除','5','工艺退回','6','总检确认') AS RecordstatusDisp, Qualityrecord.Recordstatus, Productbase.ProductName, " & vbCrLf & _
            "       TechnologistEmployee.FullName AS TechnologistName, Qualityrecord.Technologistdate, Qualityrecord.questionno, Qualityrecord.solvenotes, Qualityrecord.CheckValidateNotes, Qualityrecord.TechnologistUrl, Qualityrecord.RevokeNotes " & vbCrLf & _
            "FROM Qualityrecord" & vbCrLf & _
            "INNER JOIN Container ON Qualityrecord.Containerid = Container.Containerid" & vbCrLf & _
            "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
            "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
            "INNER JOIN Product ON Container.Productid = Product.Productid " & _
            "INNER JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid " & _
            "INNER JOIN Employee ON Employee.Employeeid = Qualityrecord.Checkemployeeid " & vbCrLf & _
            "LEFT OUTER JOIN Employee TechnologistEmployee ON TechnologistEmployee.Employeeid = Qualityrecord.Technologistemployeeid " & vbCrLf & _
            "WHERE 1=1 " & vbCrLf

            If strContainerid <> "" Then
                strSQL = strSQL & "AND Container.Containerid = '" & strContainerid & "'" & vbCrLf
            End If
            If strSpecid <> "" Then
                strSQL = strSQL & "AND Spec.Specid = '" & strSpecid & "'" & vbCrLf
            End If

            '质量记载单状态为不合格审理确认
            strSQL = strSQL & " AND Qualityrecord.Recordstatus IN (3,6) AND Qualityrecord.Qualityrecordid NOT IN (SELECT DISTINCT Qualityrecordid FROM scrapinfo where Qualityrecordid is not null)"

            strSQL = strSQL & " ORDER BY Qualityrecord.Recordstatus DESC, Qualityrecord.Checkdate DESC" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", dtMain)


            strSQL = "SELECT distinct Qualityrecord.Qualityrecordid, (CASE WHEN Productnoinfo.Productno = '00000000' THEN '' ELSE Productnoinfo.Productno END) AS Productno," & vbCrLf & _
            "       Productnoinfo.Qualityrecordnotes, Qualitydisposeadvice.Qualitydisposeadvicename, Productnoinfo.Qualitydisposenotes,(CASE WHEN Productnoinfo.Qty IS NULL THEN 1 ELSE Productnoinfo.Qty END) AS ScrapQty, " & vbCrLf & _
            "       (CASE WHEN Scrapinfochildcontainer.Childcontainer IS NOT NULL THEN '已报废' ELSE '未报废' END) AS BaofeiStatus," & vbCrLf & _
            "       (CASE WHEN scrapinfo.Scrapqty IS NOT NULL THEN '已报废' ELSE '未报废' END) AS Status" & vbCrLf & _
            "FROM Qualityrecord" & vbCrLf & _
            "INNER JOIN Container ON Qualityrecord.Containerid = Container.Containerid" & vbCrLf & _
            "INNER JOIN Spec ON Qualityrecord.Specid = Spec.Specid" & vbCrLf & _
            "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
            "INNER JOIN Employee ON Employee.Employeeid = Qualityrecord.Checkemployeeid" & vbCrLf & _
            "LEFT OUTER JOIN QualityRecordProductNoInfo ON QualityRecordProductNoInfo.Qualityrecordid = Qualityrecord.Qualityrecordid" & vbCrLf & _
            "LEFT OUTER JOIN Productnoinfo ON Productnoinfo.Productnoinfoid = QualityRecordProductNoInfo.Productnoinfoid" & vbCrLf & _
            "LEFT OUTER JOIN Qualitydisposeadvice ON Productnoinfo.Qualitydisposeadviceid = Qualitydisposeadvice.Qualitydisposeadviceid" & vbCrLf & _
            "LEFT OUTER JOIN Scrapinfochildcontainer ON Scrapinfochildcontainer.Childcontainer = Productnoinfo.Productno" & vbCrLf & _
            "LEFT OUTER JOIN scrapinfo ON scrapinfo.specid=Spec.Specid" & vbCrLf & _
            "WHERE 1=1 " & vbCrLf
            If strContainerid <> "" Then
                strSQL = strSQL & "AND Container.Containerid = '" & strContainerid & "'" & vbCrLf
            End If
            If strSpecid <> "" Then
                strSQL = strSQL & "AND Spec.Specid = '" & strSpecid & "'" & vbCrLf
            End If

            Dim dtDetail As DataTable = dal.GetCheckPointResult(strSQL)

            If dtDetail.Rows.Count > 0 Then
                For i = 0 To dtDetail.Rows.Count - 1
                    If dtDetail.Rows(i).IsNull("Qualityrecordnotes") = False Then
                        Dim strQuality As String
                        strQuality = dtDetail.Rows(i).Item("Qualityrecordnotes")
                        dtDetail.Rows(i).Item("Qualityrecordnotes") = strQuality
                    End If
                Next
            End If

            Dim ds As New DataSet
            ds.Tables.Add(dtMain.Copy())
            ds.Tables(0).TableName = "dtMain"
            ds.Tables.Add(dtDetail.Copy())
            ds.Tables(1).TableName = "dtDetail"

            Dim parentColunms = New DataColumn(0) {ds.Tables(0).Columns("Qualityrecordid")}
            Dim childColunms = New DataColumn(0) {ds.Tables(1).Columns("Qualityrecordid")}
            ds.Relations.Add("rs0", parentColunms, childColunms)
            oResult.DBDataSet = ds
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取报废信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetScrapInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetScrapInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid, strSpecid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("ContainerID") = True Then
                strContainerid = QueryCondition("ContainerID").ToString()
            End If
            strSpecid = ""
            If QueryCondition.Keys.Contains("SpecID") = True Then
                strSpecid = QueryCondition("SpecID").ToString()
            End If

            'strSQL = "SELECT container.containername,Scrapinfo.Scrapinfoid, Scrapinfo.Scrapinfoname, Container.Containername, Specbase.Specname, Scrapinfo.Scrapqty," & vbCrLf & _
            '        "       Employee.Fullname,null lossreasonname, Scrapinfo.Submitdate, Scrapinfo.Notes" & vbCrLf & _
            '        "FROM Scrapinfo" & vbCrLf & _
            '        "INNER JOIN Container ON Scrapinfo.Containerid = Container.Containerid" & vbCrLf & _
            '        "INNER JOIN Spec ON Scrapinfo.Specid = Spec.Specid" & vbCrLf & _
            '        "INNER JOIN Specbase ON Spec.Specbaseid = Specbase.Specbaseid" & vbCrLf & _
            '        "INNER JOIN Employee ON Employee.Employeeid = Scrapinfo.Submitemployeeid" & vbCrLf & _
            '        "LEFT OUTER JOIN Qualityrecord ON Qualityrecord.Qualityrecordid = Scrapinfo.Qualityrecordid" & vbCrLf & _
            '        "WHERE Container.ContainerID = '" & strContainerid & "'" & vbCrLf & _
            '        "AND Spec.SpecID ='" & strSpecid & "'" & vbCrLf
            'add:Wangjh 2017-8-22
            'strSQL += "  UNION ALL"
            strSQL += "  SELECT c.containername,qhd.qty Scrapqty,e2.fullname fullname,l.lossreasonname,qhd.Checkdate Submitdate FROM qtyhistorydetails qhd" &
                        " LEFT JOIN qtyhistory qh ON qh.qtyhistoryid=qhd.qtyhistoryid" &
                        " LEFT JOIN lossreason l ON l.lossreasonid=qhd.reasoncodeid" &
                        " LEFT JOIN historymainline hml ON hml.txnid=qhd.txnid" &
                      "  LEFT JOIN employee e2 ON e2.employeeid=qhd.checkerid" &
                      " LEFT JOIN container c ON c.containerid=qhd.containerid"

            strSQL += String.Format(" WHERE c.containerid='{0}' and qhd.changeqtytype=2", QueryCondition("ContainerID"))
            If Not String.IsNullOrWhiteSpace(QueryCondition("WorkFlowStepID")) Then
                strSQL += String.Format(" and hml.workflowstepid='{0}'", QueryCondition("WorkFlowStepID"))
            Else
                strSQL += String.Format(" and hml.specid='{0}'", QueryCondition("SpecID"))
            End If
            strSQL += " GROUP BY c.containername,qhd.qty,e2.fullname,l.lossreasonname,hml.txndate,qhd.checkdate,hml.txndate"
            strSQL += " UNION "

            strSQL += <string>SELECT  c.containername,abs(ch.qty) Scrapqty,e.fullname,l.lossreasonname,ch.createdate Submitdate     FROM Checkqtychangerecord ch
	                    LEFT JOIN container c ON c.containerid=ch.containerid 
	                    LEFT JOIN employee e ON e.employeeid=ch.employeeid
	                    LEFT JOIN lossreason l ON l.lossreasonid=ch.reasonid
                        WHERE ch.changeqtytype=2 and ch.isuse = 0
                      </string>.Value
            strSQL += String.Format(" and c.containerid='{0}' and ch.workflowstepid='{1}'", QueryCondition("ContainerID"), QueryCondition("WorkFlowStepID"))
            strSQL += " GROUP BY c.containername,ch.qty,e.fullname,l.lossreasonname,ch.createdate"

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取物料信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetMaterialInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetMaterialInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If

            strSQL = "SELECT Container.Containername,CMaterialinfo.MaterialName, CMaterialinfo.Materialno," & vbCrLf & _
                    "       CMaterialinfo.Materialsize, CMaterialinfo.MaterialSpecification,  CMaterialinfo.MaterialTrademark" & vbCrLf & _
                    "FROM Container" & vbCrLf & _
                    "INNER JOIN Product ON Product.Productid = Container.Productid" & vbCrLf & _
                    "INNER JOIN Productbase ON Productbase.Productbaseid = Product.Productbaseid" & vbCrLf & _
                    "INNER JOIN Producttype ON Producttype.Producttypeid = Product.Producttypeid" & vbCrLf & _
                    "INNER JOIN" & vbCrLf & _
                    "(SELECT Historymainline.Containerid, Materialinfo.MaterialName, Materialinfo.Materialno," & vbCrLf & _
                    "        Materialinfo.Materialsize, Materialinfo.MaterialSpecification,Materialinfo.MaterialTrademark" & vbCrLf & _
                    "FROM Datapointhistory " & vbCrLf & _
                    "INNER JOIN Datapointhistorydetail ON Datapointhistorydetail.Datapointhistoryid = Datapointhistory.Datapointhistoryid" & vbCrLf & _
                    "INNER JOIN Historymainline ON Datapointhistory.Historymainlineid = Historymainline.Historymainlineid" & vbCrLf & _
                    "INNER JOIN Materialinfo ON Datapointhistorydetail.Datavalue = Materialinfo.Materialinfoid" & vbCrLf & _
                    "WHERE Historymainline.Status = 1" & vbCrLf & _
                    ") CMaterialinfo" & vbCrLf & _
                    "ON CMaterialinfo.Containerid = Container.Containerid" & vbCrLf & _
                    "WHERE Container.ContainerID = '" & strContainerid & "'" & vbCrLf

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("Specname", dtMain)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取结活信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerComInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetContainerComInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If

            'UPDATE:Wangjh 2017-8-23 注释
            'strSQL = "SELECT Container.Containername, Specbase.SpecName, NVL(CReport.Qty, 0) AS ReportQty,  NVL(CEligible.Eligibilityqty, 0) AS Eligibilityqty, " & vbCrLf & _
            '        "        NVL(CQualityrecord.QualityrecordQty, 0) AS QualityrecordQty,  NVL(CScrapinfo.Scrapqty, 0) AS Scrapqty,CReport.Historymainlineid" & vbCrLf & _
            '        "FROM Container" & vbCrLf & _
            '        "INNER JOIN  " & vbCrLf & _
            '        "(select distinct c.containerid,cws.specid,speclastcheckhistory.qualifiedqty as Qty,Historymainline.Historymainlineid" & vbCrLf & _
            '       "from container c " & vbCrLf & _
            '      " left join currentstatus ccs on ccs.currentstatusid=c.currentstatusid" & vbCrLf & _
            '      " left join workflowstep ws on ws.workflowstepid=ccs.workflowstepid" & vbCrLf & _
            '      " left join workflow wf on wf.workflowid=ws.workflowid" & vbCrLf & _
            '      " left join cdodefinition cdo on cdo.cdodefid=wf.cdotypeid " & vbCrLf & _
            '      " Left  Join " & vbCrLf & _
            '      "(" & vbCrLf & _
            '       "select ws1.workflowstepid, ws1.sequence, ws1.workflowid, CASE ws1.specid WHEN  '0000000000000000' THEN " & vbCrLf & _
            '       "(SELECT revofrcdid FROM specbase WHERE specbaseid = ws1.specbaseid) ELSE ws1.specid END AS specid " & vbCrLf & _
            '       "from workflowstep ws1" & vbCrLf & _
            '       "left join spec s1 on s1.specid=ws1.specid " & vbCrLf & _
            '       ") cws on cws.workflowid=ws.workflowid" & vbCrLf & _
            '       "left join spec s on s.specid=cws.specid" & vbCrLf & _
            '        "left join specbase sb on sb.specbaseid=s.specbaseid" & vbCrLf & _
            '        "inner join Historymainline on Historymainline.Containerid=c.containerid and cws.workflowstepid=Historymainline.Workflowstepid" & vbCrLf & _
            '        "inner join speclastcheckhistory on Historymainline.Historymainlineid=speclastcheckhistory.historymainlineid" & vbCrLf & _
            '        "where c.status=1 and c.parentcontainerid is null and c.containerid <> c.containername and cdo.cdoname = 'MachineMfgWorkFlow' " & vbCrLf & _
            '        "and c.containerid='" + strContainerid + "' and Historymainline.Reversalstatus=1 " & vbCrLf & _
            '        ") CReport  " & vbCrLf & _
            '        "ON CReport.Containerid = Container.Containerid " & vbCrLf & _
            '        "LEFT OUTER JOIN  " & vbCrLf & _
            '        "(SELECT WorkCenterDispatching.Containerid, WorkCenterDispatching.Specid, NVL(SUM(Conventioncheckhistory.Eligibilityqty), 0) AS Eligibilityqty " & vbCrLf & _
            '        "FROM WorkCenterDispatching  " & vbCrLf & _
            '        "INNER JOIN Workingreporthistory ON WorkCenterDispatching.WorkCenterDispatchingid = Workingreporthistory.WorkCenterDispatchingid " & vbCrLf & _
            '        "INNER JOIN Conventioncheckhistory ON Workingreporthistory.Workingreporthistoryid = Conventioncheckhistory.Workingreportid " & vbCrLf & _
            '        "INNER JOIN Historymainline ON Historymainline.Historymainlineid = Conventioncheckhistory.Historymainlineid " & vbCrLf & _
            '        "WHERE Historymainline.Reversalstatus = 1 " & vbCrLf & _
            '        "AND WorkCenterDispatching.Containerid='" + strContainerid + "'" & vbCrLf & _
            '        "GROUP BY WorkCenterDispatching.Containerid, WorkCenterDispatching.Specid" & vbCrLf & _
            '        ") CEligible  " & vbCrLf & _
            '        "ON CEligible.Containerid = Container.Containerid" & vbCrLf & _
            '        "AND CEligible.Specid = CReport.Specid" & vbCrLf & _
            '        "LEFT OUTER JOIN  " & vbCrLf & _
            '        "(SELECT Scrapinfo.Containerid, Scrapinfo.Specid,SUM(Scrapqty) AS Scrapqty " & vbCrLf & _
            '        "FROM Scrapinfo " & vbCrLf & _
            '        "inner join container on container.containerid=Scrapinfo.Containerid " & vbCrLf & _
            '        "inner join historymainline on historymainline.containerid=container.containerid " & vbCrLf & _
            '        "WHERE Scrapinfo.Containerid='" + strContainerid + "' and Historymainline.Reversalstatus=1 " & vbCrLf & _
            '        "GROUP BY Scrapinfo.Containerid, Scrapinfo.Specid" & vbCrLf & _
            '        ") CScrapinfo " & vbCrLf & _
            '        "ON CScrapinfo.Containerid = Container.Containerid" & vbCrLf & _
            '        "AND CScrapinfo.Specid = CReport.Specid" & vbCrLf & _
            '        "LEFT OUTER JOIN " & vbCrLf & _
            '        "(SELECT Qualityrecord.Containerid, Qualityrecord.Specid, SUM(Qualityrecord.Qty) AS QualityrecordQty " & vbCrLf & _
            '        "FROM Qualityrecord " & vbCrLf & _
            '        "inner join container on container.containerid=Qualityrecord.Containerid " & vbCrLf & _
            '        "inner join historymainline on historymainline.containerid=container.containerid " & vbCrLf & _
            '        "WHERE Qualityrecord.Containerid='" + strContainerid + "' and Historymainline.Reversalstatus=1  " & vbCrLf & _
            '        "GROUP BY Qualityrecord.Containerid, Qualityrecord.Specid" & vbCrLf & _
            '        ") CQualityrecord " & vbCrLf & _
            '        "ON CQualityrecord.Containerid = Container.Containerid" & vbCrLf & _
            '        "AND CQualityrecord.Specid = CReport.Specid" & vbCrLf & _
            '        "INNER JOIN Spec ON Spec.Specid = CReport.Specid" & vbCrLf & _
            '        "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
            '        "WHERE Container.ContainerID = '" + strContainerid + "' order by CReport.Historymainlineid asc" & vbCrLf
            'add:Wangjh 2017-8-23  
            '修改：增加了报工人、报工时间、检验类型查询并删除usecompleted=10的判断 2018年12月11日 田丰维
            strSQL = "SELECT csr.containername,sb.specname,csr.reportqty,csr.passqty eligibilityqty,cno.zbhgps qualityrecordqty," &
                    " csr.lossqty scrapqty,em.fullname workername,csr.reportdate," &
                    " CASE WHEN (csr.usecompleted = 1 AND csw.containerfinishedcheckinfoid IS NULL AND csw.checktype = 1) THEN '首检'" &
                    " WHEN (csr.usecompleted = 1 AND csw.containerfinishedcheckinfoid IS NULL) THEN '工序检'" &
                    " WHEN(csr.usecompleted = 1 AND csw.containerfinishedcheckinfoid IS NOT NULL) THEN '成品检'" &
                    " WHEN csr.usecompleted = 0 THEN '' END AS  CheckType" &
                    " FROM containersteprecord csr" &
                    " LEFT JOIN spec s ON s.specid=csr.specid" &
                    " LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid " &
                    " LEFT JOIN employee em ON em.employeeid = csr.reportemployeeid" &
                    " LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = csr.containersteprecordid" &
                    " LEFT JOIN containernonconforminginfo cno ON cno.containerid=csr.containerid AND cno.workflowstepid=csr.workflowstepid AND cno.checkemployeeid IS NULL"
            strSQL += String.Format(" WHERE csr.containerid='{0}'", strContainerid)
            strSQL += " ORDER BY csr.specno"
            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            'dtMain = DataAdjust.FormatDataSetSpecNameByColumName("Specname", dtMain)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    ''' <summary>
    ''' 获取物料准备信息(弹出界面)
    ''' </summary>
    ''' <returns></returns>
    Private Function GetMaterialReadyInfoForPopup(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMESContainerReportIBusiness.GetMaterialReadyInfoForPopup
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL, strContainerid As String

            strContainerid = ""
            If QueryCondition.Keys.Contains("Containerid") = True Then
                strContainerid = QueryCondition("Containerid").ToString()
            End If

            strSQL = "SELECT distinct Container.Containerid, Container.Containername, Specbase.Specname, CSpec.Sequence,CSpec.Workflowstepname,CSpec.Specid,Container.qty," & vbCrLf & _
                "       Factory.factoryid, Product.Productid, Productbase.productname,Product.description, Factory.factoryname,Container.plannedstartdate,Container.plannedcompletiondate," & vbCrLf & _
                "       Historymainline.Txndate AS operdate,Employee.fullname AS operEmp, Historymainline.historymainlineid, '' as Fullname,cdo1.cdoname,'' as txttype,movehistory.containerchooserid,movehistory.notes,container.originalqty" & vbCrLf & _
                "FROM Container" & vbCrLf & _
                "INNER JOIN Historymainline ON Container.Containerid = Historymainline.Containerid" & vbCrLf & _
                "LEFT JOIN Product ON Container.Productid = Product.Productid" & vbCrLf & _
                "LEFT JOIN Productbase ON Product.Productbaseid = Productbase.Productbaseid" & vbCrLf & _
                "LEFT JOIN Factory on Factory.Factoryid = Container.Originalfactoryid" & vbCrLf & _
                "INNER JOIN " & vbCrLf & _
                "(" & vbCrLf & _
                "SELECT  Workflowstep.Sequence,Workflowstep.Workflowstepid,Workflowstep.Workflowstepname, Workflowstep.Workflowid, " & vbCrLf & _
                "(CASE WHEN Workflowstep.Specid = '0000000000000000'" & vbCrLf & _
                " THEN Specbase.Revofrcdid ELSE Workflowstep.Specid END) AS Specid" & vbCrLf & _
                "FROM Workflowstep " & vbCrLf & _
                "LEFT JOIN Specbase ON Specbase.Specbaseid = Workflowstep.Specbaseid" & vbCrLf & _
                ") CSpec" & vbCrLf & _
                "ON CSpec.Workflowstepid = Historymainline.Workflowstepid" & vbCrLf & _
                "INNER JOIN Spec ON Spec.Specid = CSpec.Specid" & vbCrLf & _
                "INNER JOIN Specbase ON Specbase.Specbaseid = Spec.Specbaseid" & vbCrLf & _
                "LEFT JOIN ContainerChooser ON ContainerChooser.Containerid = Container.Containerid AND ContainerChooser.Specid = Spec.Specid" & vbCrLf & _
                "LEFT JOIN Employee AssignedEmployee ON ContainerChooser.Assignedemployeeid = AssignedEmployee.Employeeid" & vbCrLf & _
                "LEFT JOIN Employee ON Employee.Employeeid = Historymainline.Employeeid" & vbCrLf & _
                "INNER JOIN Workflow ON Workflow.Workflowid = CSpec.Workflowid" & vbCrLf & _
                "INNER JOIN Cdodefinition ON Workflow.Cdotypeid = Cdodefinition.Cdodefid" & vbCrLf & _
                "INNER JOIN cdodefinition cdo1 ON Historymainline.txntype = cdo1.cdodefid" & vbCrLf & _
                "left join movehistory on movehistory.historymainlineid=Historymainline.Historymainlineid" & vbCrLf & _
                "WHERE Cdodefinition.cdoname = 'Workflow'" & vbCrLf & _
                "AND (cdo1.cdoname = 'MoveStd' or cdo1.cdoname = 'MoveNonStd') AND Historymainline.reversalstatus = 1" & vbCrLf & _
                "AND Container.ContainerID = '" + strContainerid + "' ORDER BY Historymainline.Txndate,Historymainline.historymainlineid asc"

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)
            dtMain = DataAdjust.FormatDataSetSpecNameByColumName("Specname", dtMain)

            Dim dv As DataView = New DataView(dtMain)
            Dim condt As DataTable = dv.ToTable(True, "containername", "productname",
                      "description", "factoryname", "plannedstartdate",
                      "plannedcompletiondate", "containerid", "qty", "originalqty")

            Dim stepdt = New DataTable("stepinfo")
            stepdt.Columns.Add("stepname")
            stepdt.Columns.Add("specname")
            stepdt.Columns.Add("operEmp")
            stepdt.Columns.Add("operdate")
            stepdt.Columns.Add("containerid")
            stepdt.Columns.Add("historymainlineid")
            '//遍历批次表，添加无件号的批次列,并判断拖期颜色
            stepdt.Columns.Add("datacollection")
            stepdt.Columns.Add("div")
            stepdt.Columns.Add("Fullname")
            stepdt.Columns.Add("txntype")
            For i = 0 To dtMain.Rows.Count - 1


                Dim _stepdr As DataRow = stepdt.NewRow()
                _stepdr("stepname") = dtMain.Rows(i)("workflowstepname")
                _stepdr("specname") = dtMain.Rows(i)("specname")
                _stepdr("containerid") = dtMain.Rows(i)("containerid")
                _stepdr("historymainlineid") = dtMain.Rows(i)("historymainlineid")
                If (dtMain.Rows(i).IsNull("operEmp")) Then
                    _stepdr("operEmp") = ""
                    _stepdr("operdate") = dtMain.Rows(i)("operdate")
                    _stepdr("div") = "<div style=""width:97%;height:97%;background-color:Green;""></div>"
                Else
                    _stepdr("operEmp") = dtMain.Rows(i)("operEmp")
                    _stepdr("operdate") = dtMain.Rows(i)("operdate")
                    _stepdr("div") = "<div style=""width:97%;height:97%;background-color:Green;""></div>"
                End If

                Dim cdoename As String = dtMain.Rows(i)("cdoname").ToString()
                If (cdoename <> "MoveNonStd") Then
                    _stepdr("Fullname") = GetFullname(dtMain.Rows(i)("containerid").ToString(), dtMain.Rows(i)("Specid").ToString(), dtMain.Rows(i)("containerchooserid").ToString())
                End If

                '处理类型
                If (cdoename = "MoveStd") Then
                    _stepdr("txntype") = "提交"
                ElseIf (cdoename = "MoveNonStd") Then
                    If dtMain.Rows(i)("operEmp").ToString = dtMain.Rows(i - 1)("operEmp").ToString Then
                        _stepdr("txntype") = "召回"
                    Else
                        _stepdr("txntype") = "退回"
                    End If
                End If

                stepdt.Rows.Add(_stepdr)
            Next

            Dim ds As New DataSet
            ds.Tables.Add(condt)
            ds.Tables(0).TableName = "containerinfo"
            ds.Tables.Add(stepdt)
            '//  建立 关系
            Dim rs As DataRelation = New DataRelation("rs", ds.Tables("containerinfo").Columns("containerid"), ds.Tables("stepinfo").Columns("containerid"))
            ds.Relations.Add(rs)
            oResult.DBDataSet = ds
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try

    End Function

    Private Function GetFullname(ByVal containerid As String, ByVal Specid As String, ByVal containerchooserid As String) As String
        Dim result As String
        Dim Sql As String
        Sql = "select ch.*,e.fullname" & vbCrLf & _
               "from employee e" & vbCrLf & _
               "inner join containerchooser ch on ch.assignedemployeeid=e.employeeid" & vbCrLf & _
               "where ch.containerchooserid='" + containerchooserid + "'" & vbCrLf
        Dim dt As DataTable = dal.GetQualityRecordInfoForProductNo(Sql)
        If (dt.Rows.Count > 0) Then
            result = dt.Rows(0).Item("fullname").ToString()
        Else
            result = ""
        End If
        Return result
    End Function

    ''' <summary>
    ''' 根据车间获取工段列表
    ''' </summary>
    ''' <param name="factoryname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetWorkcenterByFactory(ByVal factoryname As String) As uMESProcessResult Implements uMESContainerReportIBusiness.GetWorkcenterByFactory
        Dim oResult As New uMESProcessResult

        Try
            Dim strSQL As String
            strSQL = "select t.workcentername,t.workcenterid  from workcenter t inner join factory f on f.factoryid=t.factoryid where f.factoryname='" + factoryname + "' "

            Dim dtMain As DataTable = dal.GetQualityRecordInfoForProductNo(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function
    ''' <summary>
    ''' 获取批次加工信息（弹出页面）
    ''' create:Wangjh 2017-8-22
    ''' modifiy:zhunan 2018-4-26
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetContianerWorkingInfo(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable Implements uMESContainerReportIBusiness.GetContainerWorkingInfo
        'Dim strSql As String = " SELECT csr.dispatchdate,csr.dispatchemployeename,csr.dispatchqty,csr.dispatchresourcename,csr.reportdate,e1.employeename," &
        '                       " NVL( csr.reportemployeename,E.FULLNAME) reportemployeename,csr.reportqty,csr.teamname,DECODE(wcd.Status, 0, '已派工','1','已接收','2','已报工','3','已完成','4','已关闭') AS Ckpgdstatusname," &
        '                        " (CASE WHEN wrh.ReportType2 IS NULL THEN  DECODE(csw.checktype,1,'首检',2,'工序检') ELSE  DECODE(wrh.Reporttype2,'0','首件','1','中件','2','末件','3','其他') END) checktypename," &
        '                       " csr.checkdate,csr.checkemployeename,csr.passqty,csr.lossqty FROM containersteprecord csr " &
        '                        " LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid=csr.containersteprecordid" &
        '                        " LEFT JOIN WorkCenterDispatching wcd ON wcd.workcenterdispatchingid=csr.dispatchid" &
        '                        " LEFT JOIN Workingreporthistory wrh ON wrh.workingreporthistoryid=csr.containersteprecordid" &
        '                        " LEFT JOIN CONTAINERSTEPDISPATCH CSD ON CSD.CONTAINERID=CSR.CONTAINERID AND CSD.WORKFLOWSTEPID=CSR.WORKFLOWSTEPID" &
        '                        " LEFT JOIN CONTAINERSTEPDISPATCHDetail CSDD ON CSDD.CONTAINERSTEPDISPATCHID=CSD.CONTAINERSTEPDISPATCHID" &
        '                        " LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID=CSD.Workerid" &
        '                        " LEFT JOIN EMPLOYEE e1 ON e1.employeeid=csdd.Workerid"
        'strSql += String.Format(" WHERE csr.containerid='{0}'", QueryCondition("ContainerID"))
        'If Not String.IsNullOrWhiteSpace(QueryCondition("WorkFlowStepID")) Then
        '    strSql += String.Format(" and csr.workflowstepid='{0}'", QueryCondition("WorkFlowStepID"))
        'Else
        '    strSql += String.Format(" and csr.specid='{0}'", QueryCondition("SpecID"))
        'End If
        'strSql += " GROUP BY csr.dispatchdate,csr.dispatchemployeename,csr.dispatchqty,csr.dispatchresourcename,csr.reportdate,e1.employeename," &
        '    " NVL( csr.reportemployeename,E.FULLNAME),csr.reportqty,csr.teamname,wcd.Status,wrh.ReportType2,csw.checktype,wrh.ReportType2," &
        '    "wrh.Reporttype2,csr.checkdate,csr.checkemployeename,csr.passqty,csr.lossqty"
        'Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        'GetContianerWorkingInfo = dt
        Dim sb As StringBuilder = New StringBuilder()
        sb.AppendLine("SELECT csd.containerstepdispatchid,csr.dispatchdate,csr.dispatchemployeename,csr.dispatchqty,csr.dispatchresourcename,csr.reportdate,")
        sb.AppendLine("       csr.reportemployeename,csr.reportqty,csr.teamname,")
        sb.AppendLine("       DECODE(wcd.Status, 0, '已派工','1','已接收','2','已报工','3','已完成','4','已关闭') AS Ckpgdstatusname,")
        sb.AppendLine("       (CASE WHEN wrh.ReportType2 IS NULL THEN  DECODE(csw.checktype,1,'首件报工',3,'试件报工',4,'返修报工','其他报工') ELSE  DECODE(wrh.Reporttype2,'0','首件','1','中件','2','末件','3','其他') END) checktypename,")
        sb.AppendLine("       csr.checkdate,csr.checkemployeename,csr.passqty,csr.lossqty")
        sb.AppendLine("FROM containersteprecord csr")
        'add csw.isuse = 0,csd.isuse= 0 by tianFW 2018年9月29日
        sb.AppendLine("LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid=csr.containersteprecordid and csw.isuse = 0")
        sb.AppendLine("LEFT JOIN containerstepdispatch csd ON csd.containerstepdispatchid=csr.dispatchid and csd.isuse = 0")
        sb.AppendLine("LEFT JOIN WorkCenterDispatching wcd ON wcd.workcenterdispatchingid=csr.dispatchid")
        sb.AppendLine("LEFT JOIN employee e ON e.employeeid=csd.Workerid")
        sb.AppendLine("LEFT JOIN Workingreporthistory wrh ON wrh.workingreporthistoryid=csr.containersteprecordid")
        sb.AppendFormat(" WHERE csr.containerid='{0}'", QueryCondition("ContainerID"))
        If Not String.IsNullOrWhiteSpace(QueryCondition("WorkFlowStepID")) Then
            sb.AppendFormat(" AND csr.workflowstepid='{0}'", QueryCondition("WorkFlowStepID"))
        Else
            sb.AppendFormat(" and csr.specid='{0}'", QueryCondition("SpecID"))
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())

        dt.Columns.Add("employeename", GetType(String)) '派给工人

        '查询工序派给员工信息
        sb = New StringBuilder()
        sb.AppendLine("SELECT DISTINCT csd.containerstepdispatchid,NVL(e.fullname,e1.fullname) fullname")
        sb.AppendLine("FROM containersteprecord csr")
        sb.AppendLine("LEFT JOIN containerstepdispatch csd ON csd.containerstepdispatchid=csr.dispatchid and csd.isuse = 0 ")
        sb.AppendLine("LEFT JOIN containerstepdispatchdetail csdd ON csdd.containerstepdispatchid=csd.containerstepdispatchid")
        sb.AppendLine("LEFT JOIN employee e ON e.employeeid=csdd.Workerid")
        sb.AppendLine("LEFT JOIN employee e1 ON e1.employeeid = csd.workerid")
        sb.AppendFormat(" WHERE csr.containerid='{0}'", QueryCondition("ContainerID"))
        If Not String.IsNullOrWhiteSpace(QueryCondition("WorkFlowStepID")) Then
            sb.AppendFormat(" AND csr.workflowstepid='{0}'", QueryCondition("WorkFlowStepID"))
        Else
            sb.AppendFormat(" and csr.specid='{0}'", QueryCondition("SpecID"))
        End If

        Dim dtSpecDispatchToPerson = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
        For Each dr As DataRow In dt.Rows
            Dim filter = String.Format("containerstepdispatchid = '{0}'", dr("containerstepdispatchid"))
            Dim datarow = dtSpecDispatchToPerson.Select(filter)
            Dim employeelist As StringBuilder = New StringBuilder()
            If datarow.Length > 0 Then
                For Each item As DataRow In datarow
                    employeelist.Append(item("fullname").ToString() + ",")
                Next
                employeelist.Remove(employeelist.Length - 1, 1)

            End If
            dr("employeename") = employeelist
        Next
        GetContianerWorkingInfo = dt
    End Function
    ''' <summary>
    ''' 获取成品检验信息
    ''' create:Wangjh 2017-8-22
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCheckFinishInfo(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable Implements uMESContainerReportIBusiness.GetCheckFinishInfo
        Dim strSql As String = "  SELECT cfci.acceptanceqty,e.fullname,cfci.defectqty,cfci.reworkqty,cfci.lossqty,cfci.createdate FROM containerfinishedcheckinfo cfci" &
                                " LEFT JOIN employee e ON e.employeeid=cfci.checkerid"
        strSql += String.Format(" WHERE cfci.containerid='{0}'", QueryCondition("ContainerID"))
        If Not String.IsNullOrWhiteSpace(QueryCondition("WorkFlowStepID")) Then
            strSql += String.Format(" and cfci.workflowstepid='{0}'", QueryCondition("WorkFlowStepID"))
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        GetCheckFinishInfo = dt
    End Function
    ''' <summary>
    ''' 获取批次基本信息
    ''' create:Wangjh 2017-8-23
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetContianerBaseInfo(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable Implements uMESContainerReportIBusiness.GetContianerBaseInfo
        Dim strSql As New StringBuilder
        strSql.AppendLine(" SELECT c.containername,pb.productname,p.description,pf.productfamilyname,pt.producttypename,m.qty planqty,c.qty wipqty,m.mfgordername,wc.workcentername||'/'||t.teamname workcenterandteam FROM container c")
        strSql.AppendLine(" LEFT JOIN product p ON p.productid=c.productid")
        strSql.AppendLine("  LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid")
        strSql.AppendLine("  LEFT JOIN productfamily pf ON pf.productfamilyid=p.productfamilyid ")
        strSql.AppendLine("  LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid")
        strSql.AppendLine(" LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid")
        strSql.AppendLine(" LEFT JOIN workcenter wc ON wc.workcenterid=c.workcenterid") '增加主制工区和班组Add by Wzj 2017/11/14
        strSql.AppendLine(" LEFT JOIN team t ON t.teamid=c.teamid") 'Add by Wzj 2017/11/14
        strSql.AppendFormat("  WHERE c.containerid='{0}'", QueryCondition("ContainerID"))

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
        GetContianerBaseInfo = dt
    End Function
    ''' <summary>
    ''' 获取批次的外委信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerCommssionInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMESContainerReportIBusiness.GetContainerCommssionInfo
        Dim strSql As String = <string>
                                  SELECT ws1.workflowstepname,ws2.workflowstepname inworkflowstepname,cm.outdate,cm.indate,e1.fullname OutName,e2.fullname InName,ct.customername,cm.acceptanceqty,cm.outqty,cm.acceptanceqty,cm.lossqty  FROM containercommissioning cm
                                    LEFT JOIN workflowstep ws1 ON ws1.workflowstepid=cm.workflowstepid
                                    LEFT JOIN Workflowstep ws2 ON ws2.workflowstepid=cm.inworkflowstepid
                                    LEFT JOIN employee e1 ON e1.employeeid=cm.outemployeeid
                                    LEFT JOIN employee e2 ON e2.employeeid=cm.inemployeeid
                                    LEFT JOIN customer ct ON ct.customerid=cm.outcustomerid
                              </string>
        strSql += " LEFT JOIN workflowstep ws3 ON ws3.workflowid=ws1.workflowid AND (ws3.sequence>=ws1.sequence AND ws3.sequence<ws2.sequence)"
        strSql += String.Format(" WHERE cm.containerid ='{0}' AND ws3.workflowstepid='{1}' AND cm.isuse=0", para("ContainerID"), para("WorkFlowStepID"))
        GetContainerCommssionInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取批次班组派工信息
    ''' add zhunan 2018.01.09
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerTeamDispathcInfo(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As System.Data.DataTable Implements IBusiness.uMESContainerReportIBusiness.GetContainerTeamDispathcInfo
        Dim strSql As String = <string>
                                SELECT ctd.workflowstepid,t.teamname
                                FROM Containerteamdispatch ctd
                                LEFT JOIN team t ON t.teamid = ctd.teamid
                               </string>
        strSql += String.Format(" WHERE ctd.containerid ='{0}'", para("ContainerID"))

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        Dim sb As New StringBuilder
        If dt.Rows.Count > 0 Then
            If dt.Rows(0).IsNull("workflowstepid") Then
                GetContainerTeamDispathcInfo = dt
            Else
                sb.AppendFormat("SELECT t.teamname FROM Containerteamdispatch ctd LEFT JOIN team t ON t.teamid = ctd.teamid WHERE ctd.containerid = '{0}' AND ctd.workflowstepid = '{1}'", para("ContainerID"), para("WorkFlowStepID"))
                GetContainerTeamDispathcInfo = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
            End If
        Else
            GetContainerTeamDispathcInfo = New DataTable()
        End If
    End Function

    ''' <summary>
    ''' 获取子工艺序对应子工艺工序完成情况 create by tianFW 2018年12月26日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWSofSWFwrokflowstepsInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMESContainerReportIBusiness.GetWSofSWFwrokflowstepsInfo
        Dim strsql As String =
            <string>
                    SELECT scu.containerid,scu.containername,scu.productdescription productdesc,scu.productname,scu.workflowname,scu.workflowrevision workflowrev
                    ,ws.workflowstepid,ws.workflowstepname,csrd.usecompleted,DECODE(css.noship,2,'不进行','') noship,scu.containername||'/'|| scu.containerno containerno
                    FROM containercurrentinfo scu 
                    --主工艺件号或分段件号关联
                    INNER JOIN containercurrentinfo cu ON NVL(CU.WORKFLOWROUTE, CU.PRODUCTNAME) = SCU.WORKFLOWROUTE
                    --查询分段件号的主件号
                    LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTNAME = CU.WORKFLOWROUTE
	                LEFT JOIN PRODUCT P ON P.PRODUCTBASEID = PB.PRODUCTBASEID
                    --子工艺件号、主工艺件号在件号-子工艺件号中的关联关系
                    INNER JOIN productsubproduct psp ON psp.subproductid = scu.productid AND psp.productid = NVL(P.PRODUCTID, CU.PRODUCTID)
                    --主件号或分段件号的当前工艺与子工艺关联关系
                    INNER JOIN stepsubstep sbs ON sbs.workflowid = cu.workflowid AND sbs.sonworkflowid = scu.workflowid
                    LEFT JOIN Workflowstep ws ON ws.workflowstepid = sbs.sonworkflowstepid
                    LEFT JOIN containersteprecord csrd ON csrd.containerid = scu.containerid AND csrd.workflowstepid = sbs.sonworkflowstepid
                    LEFT JOIN containerstepnoship css ON css.containerid = scu.containerid AND css.workflowstepid = sbs.sonworkflowstepid
                    --当前主件号或分段件号批次的件号信息
                    LEFT JOIN PRODUCT CUP ON CUP.PRODUCTID = CU.PRODUCTID
	                LEFT JOIN PRODUCTTYPE PT ON PT.PRODUCTTYPEID = CUP.PRODUCTTYPEID
                    WHERE scu.containername = replace(cu.containername,cu.productname,scu.productname) 
            </string>
        strsql += String.Format(" AND cu.containerid ='{0}' AND sbs.workflowstepid='{1}'", para("ContainerID"), para("WorkFlowStepID"))
        '如果住批次件号不是分段件号或者是分段件号但不是子工艺虚拟件
        strsql += " AND (CU.WORKFLOWROUTE IS NULL OR (CU.WORKFLOWROUTE IS NOT NULL AND PT.PRODUCTTYPENAME <> '子工艺虚拟件'))"
        strsql += " ORDER BY cu.containername,scu.containername,ws.sequence"
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 获取子工艺不加工序信息 create by tianFW 2018年12月26日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetChildWFNotProcseeWSInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMESContainerReportIBusiness.GetChildWFNotProcseeWSInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT '' containername,'' workflowstepname,em.fullname,DECODE(css.noship,2,'不进行','') noship,css.createdate")
        strsql.AppendLine("FROM containerstepnoship css")
        strsql.AppendLine("LEFT JOIN employee em ON em.employeeid = css.employeeid")
        strsql.AppendFormat(" WHERE css.containerid ='{0}' AND css.workflowstepid='{1}'", para("ContainerID"), para("WorkFlowStepID"))
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function
End Class

